Solved

Excel Formula

Posted on 2011-09-07
19
366 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 29

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
Independent Software Vendors: 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 29

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 29

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 29

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 29

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 29

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

749 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