[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Excel Formula

I am very novice excel user (real estate appraiser) working to match public records from a tax records export to properties in our database which has been exported to another spreadsheet. We have tool to do the match and get schema right, using APN (Assessor Parcel No) to match the records.  I need help with formula involves removing dashes and spaces in the APN so they will match the the way the APN is reported in our data base. The somewhat irregular nature of the APN causes problem  The  EE provided  formula =SUBSTITUTE(A2."-"."") only partly worked.
 
Here is specific question/screenshot
How do I match column A match column b. In the example below, it does not show that some APNs will have a single letter at the tail end(no space).

 Screenshoot Example of  APN Match
0
wkittils
Asked:
wkittils
  • 8
  • 6
  • 3
  • +1
4 Solutions
 
gowflowCommented:
=SUBSTITUTE(A2,"-","")
The point "." should be replaced by a coma ","
gowflow
0
 
BusyMamaCommented:
Instead of using a formula, why not do a "Find and Replace" by finding all - and leaving the replace blank?

You can do the same thing with spaces then, on both columns, and then a matching formula (if needed) should work.
0
 
akajohnCommented:
Try this:

=0&SUBSTITUTE(SUBSTITUTE(A2,"- -"," "),"-","")
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
akajohnCommented:
Warning though:

You should post the original excel file as I am not sure about the number of spaces as it is hard to count from a picture! It also makes our job easier. I had to retype all your data!
I added a Zero in front as you did not describe the data. This should work if you fill down the same formula but always do a visual check when you are done.

A>
0
 
gowflowCommented:
Sorry I read your post again and here is the formulas:

=CONCATENATE(MID(SUBSTITUTE(A2,"-",""),1,SEARCH(" ",SUBSTITUTE(A2,"-",""))),TEXT(MID(SUBSTITUTE(A2,"-",""),SEARCH(" ",SUBSTITUTE(A2,"-",""))+1,LEN(SUBSTITUTE(A2,"-",""))-SEARCH(" ",SUBSTITUTE(A2,"-",""))),"0000"))

Put this formulas in Cel B2 bearing in mind your data is in Cell A2
pls let me know if you have any problem.
gowflow
0
 
wkittilsAuthor Commented:
to akajohn's point (the actual file is better than a snapshot).

I am posting attaching a portion of the excel file

 to busy momma, any help on how to use find/replace/specific example would help. I am not lazy, just an overloaded novice involved in a new approach to real estate appraisal involving huge spreadsheets and modeling that data...all very new too me...

to gowflow, i don't see the point  .  (pun intended)...I used " and , ( ) , no  .

excel-for-test-of-apn-formula.xlsx

0
 
wkittilsAuthor Commented:
To goflow...dear mr guru, that is one long formula. thank you. I will give the various suggestions a whack and see what happens.
0
 
akajohnCommented:
Many .Thanks for the file which allowed to test.

I added my short formula and a match column.

=TEXT(A2,0)=B2 which gives true for a match and false for a wrong match.


I suggest you manually check every "FALSE" match.

A>





excel-for-test-of-apn-formula.xlsx
0
 
wkittilsAuthor Commented:
i will give this new one a whack.  a couple other appraisers and me are working on this. none are excel experts, i/we will report back in the next day.
0
 
gowflowCommented:
Longer than expected
pls chk the file attached I added col C with the formulas but your col A does not match the sample that you provided in the question as the last 4 digits are always packed by 4 like 72 gives 0072 980 gives 0980
Pls chk the file and let me know.
gowflow
excel-for-test-of-apn-formula.xlsx
0
 
BusyMamaCommented:
For future reference if needed...

Highlight the column or columns you want to search/find/replace
Launch the Find by using CTRL+F
Click on the Replace tab
Type the item you want to replace in the top box (-)
Type the item you want to replace it with in the bottom box (but in your case, leave the bottom box empty)
Click Replace All
0
 
wkittilsAuthor Commented:
thanks to all...our small group is trying to keep up. will report back by tomorrow/weekend at worst, sorry for delay.
0
 
wkittilsAuthor Commented:
Using gollow's formula below, returned an error insofar the record on line 3 (highlighted) needs a zero in front of the first 8. Can the formula be tweaked to fix this?

=CONCATENATE(MID(SUBSTITUTE(A2,"-",""),1,SEARCH(" ",SUBSTITUTE(A2,"-",""))),TEXT(MID(SUBSTITUTE(A2,"-",""),SEARCH(" ",SUBSTITUTE(A2,"-",""))+1,LEN(SUBSTITUTE(A2,"-",""))-SEARCH(" ",SUBSTITUTE(A2,"-",""))),"0000"))

 Results from Goflow Formula (1 error example)
0
 
gowflowCommented:
pls use the file the formulas is diffrent in the file
check it out if you only need the zero in front then this is no problem
let me know
gowflow
0
 
wkittilsAuthor Commented:
Using the long formula
 
=CONCATENATE(MID(SUBSTITUTE(A2,"-",""),1,SEARCH(" ",SUBSTITUTE(A2,"-",""))),TEXT(MID(SUBSTITUTE(A2,"-",""),SEARCH(" ",SUBSTITUTE(A2,"-",""))+1,LEN(SUBSTITUTE(A2,"-",""))-SEARCH(" ",SUBSTITUTE(A2,"-",""))),"0000"))

Yields the following. This is the only formula presented that comes close to working. As per below image and corresponding attached spreadsheet, row 4 (highlighted) contains an error (needs another zero between 1 & 8)

Sorry for the delay

Long Formula Test Results imagetest-results.xlsx
0
 
wkittilsAuthor Commented:
i am reposting this question (and awarding points for the work so far)
0
 
wkittilsAuthor Commented:
still reporting one error using the long formula
reposting for another whack/final tweak to the formula
0
 
gowflowCommented:
As closing this question will take some 4 days if your ancious to ge tthe solution pls post the related new question link here as I have the solution for you.
Rgds/gowflow
0
 
gowflowCommented:
Kindly post the link for the new quesiton here if you want the solution I have it !!!
gowflow
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now