Solved

Excel Formula

Posted on 2011-09-07
19
357 Views
Last Modified: 2012-05-12
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
Comment
Question by:wkittils
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
=SUBSTITUTE(A2,"-","")
The point "." should be replaced by a coma ","
gowflow
0
 
LVL 7

Expert Comment

by:BusyMama
Comment Utility
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
 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 167 total points
Comment Utility
Try this:

=0&SUBSTITUTE(SUBSTITUTE(A2,"- -"," "),"-","")
0
 
LVL 6

Expert Comment

by:akajohn
Comment Utility
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
 
LVL 29

Accepted Solution

by:
gowflow earned 167 total points
Comment Utility
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
 

Author Comment

by:wkittils
Comment Utility
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
 

Author Comment

by:wkittils
Comment Utility
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
 
LVL 6

Expert Comment

by:akajohn
Comment Utility
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
 

Author Comment

by:wkittils
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 7

Assisted Solution

by:BusyMama
BusyMama earned 166 total points
Comment Utility
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
 

Author Comment

by:wkittils
Comment Utility
thanks to all...our small group is trying to keep up. will report back by tomorrow/weekend at worst, sorry for delay.
0
 

Author Comment

by:wkittils
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:wkittils
Comment Utility
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
 

Assisted Solution

by:wkittils
wkittils earned 0 total points
Comment Utility
i am reposting this question (and awarding points for the work so far)
0
 

Author Closing Comment

by:wkittils
Comment Utility
still reporting one error using the long formula
reposting for another whack/final tweak to the formula
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Kindly post the link for the new quesiton here if you want the solution I have it !!!
gowflow
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now