Solved

Excel Formula

Posted on 2011-09-07
19
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 31

Expert Comment

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

Expert Comment

by:BusyMama
ID: 36496909
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
ID: 36496919
Try this:

=0&SUBSTITUTE(SUBSTITUTE(A2,"- -"," "),"-","")
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:akajohn
ID: 36496942
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 31

Accepted Solution

by:
gowflow earned 167 total points
ID: 36497062
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
ID: 36497077
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
ID: 36497095
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
ID: 36497148
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
ID: 36497183
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
 
LVL 31

Expert Comment

by:gowflow
ID: 36497231
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
ID: 36497235
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
ID: 36497317
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
ID: 36502406
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 31

Expert Comment

by:gowflow
ID: 36503635
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
ID: 36506746
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
ID: 36507324
i am reposting this question (and awarding points for the work so far)
0
 

Author Closing Comment

by:wkittils
ID: 36527961
still reporting one error using the long formula
reposting for another whack/final tweak to the formula
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36512377
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 31

Expert Comment

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

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

626 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