Hello,
I am a relatively new and unskilled excel user and was having problems with the Find and replace function. I am editing an excel spreadsheet with addresses and I need to replace every St and St. with the full Street, which has thousands of entries. Whenever I try to find and replace the St with no periods, it finds every word with St in it. How do I find and replace the St, and only the St
Thanks in advance
I dont think you can
Given
1) a name - St Nicholas
2) a street - New Oxford St
3) a street - Regent St.
4) a street named after somebody - St Thomas St.
replacing St would indeed change all occurrences of the above irrespective of name or address.
You might be able to isolate (copy paste) addresses into a separate worksheet to separate them from names but you'll still get problems with 4).
Depends on your data.
You might be able to use a formula to change it, e.g. the street would be the last word in the address on that line so you could use a formula to check it's "St" AND the last word on a line, but if you have
"St Columbus Building, Regent St, St Ives, Cornwall"
all on one line then you're gonna get problems.
Last edited by Special-K; 02-18-2015 at 12:10 PM.
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
you could narrow the errors down by using " st" instead of "st" (with the space before the st). but there is no actual way to do this how you're wanting. What you could do, is add a column and use a formula to look at the end of the address for "st" and replace it with "street'
A
St. James St.
b
=if(right(A1,3)="st.",left(A1,len(a1)-3)&"street",A1)
would appear as St. James street
of course, you could add additional 'if' statements to isolate other variables and drag down, but would depend on the format of the address / field contained in the cell.
Using Special-K's example using Find & Select, Replace
1. Select Options and select Match Case.
2. In Find What enter - St that is dash space St
3. In Replace With enter xxx and click on Replace All
4. In Find What enter space St
5. In Replace With enter space Street and click on Replace All.
6. In Find What enter Street.
7. In Replace With enter Street and click on Replace All.
8. In Find What enter xxx
9. In Replace With enter - St that is dash, space St and click on Replace All.
If followed exactly and in that exact order the result will be:
1) a name - St Nicholas 2) a street - New Oxford Street 3) a street - Regent Street 4) a street named after somebody - St Thomas Street
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks