Extracting the last word of a field

  • Thread starter Thread starter Name
  • Start date Start date
N

Name

Hi all,

I have an excell sheet where one column contains the address data including
the city. I want to take the city from this field and put it in another
field. The method I thought is to fiind the LAST space character, and
extract what is on the right of this space character. I try to use FIND &
RIGHT functions, but as there are more than one space characters in each
column, it does not work.

Can anybody help.

Thanks for answers
 
one way:

=MID(A1,FIND("$",SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1, 255)

But note that this won't work for cities with more than one word in
their name...
 
Thanks,

But somehow it did not work.


JE McGimpsey said:
one way:

=MID(A1,FIND("$",SUBSTITUTE(A1," ","$",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1, 255)

But note that this won't work for cities with more than one word in
their name...
Click to expand...
 
So what does "did not work" mean?

Did you get an incorrect result? No result? an error? a crash?
 
Sorry,

There was an error message that said

"The formula you typed contains an error." and then bla-bla.

My data was not at A1 but D3, and I changed the function accordingly, and
the data I want to parse is:
"Gülden Sokak Savcý Apt. No.4 D.1 Selamiçeþme"

Please note you may not see some letters as they are different then ASCII.
 
Probably bad linewrap. Try

=MID(A1,FIND("$",SUBSTITUTE(A1,"_", "$", LEN(A1) - LEN(SUBSTITUTE(A1,
"_", ""))))+1, 255)

where I've substituted underscores (_) for space characters. Change them
back to spaces.
 
I will try it.
It seems it is the same, I had copied and pasted, this time I will write it.
I assume I will insert a column near the one I want to parse, and enter the
function at that column.

Regards,
 
Yes, you have to use your version's list separator character. In the US
it's a comma, in many european countries it's the semicolon.
 
I guess a simpler way of dealing with this would be
* Let's assume the column which contains the address data
is A
*Select the complete column
*Go to Data>Text to Columns..
*Select the "Delimited" under the Original Data type,
*click Next
*Under Delimiters in the Step 2 of the screen, select the
delimiter of your choice
*click Next
*Now you can have a preview of how it would look like once
the Address data has been separated.
*click Finish.
*you have a separate column with each set of the address.
Voila! That should be what you want
Prerequisite is that, the column B(the subsequent column)
must be empty, if not, plz insert a column.
Ex: "#12, Main street, London, United Kingdom" in A1
would be like( with comma as a delimiter)
A1 would contain #12
B1 would contain Main street
C1 London
D1 United Kingdom

There's plenty you can play with here.. with Fixed width
and Delimited.... keep trying it will answer most of you
future questions as well.

Thanks,
harsha
 
No Marsha,

I tried it and it did not work. Because the address may have few or more
instances of "space" characters in each row.
One adress may be "Road1 No:3 City1" and the second row may have "Road 21
Grand Azur Hotel Residances Suite #5 City2"

Besides the person who enters data may enter data as Road1 in one instance
and as Road 1 in the next.

It would be easier if there would be a FINDBACK function that would also
parse right to left.
Regards
 
Hmmm!!
I am not that good with formulae, but possibly I have a
not so efficient workaround..
Before running the TEXT to Column exercise, with comma as
the delimiter,
*again, assume our address data is in column A
*go to column B, corresponding row, use RIGHT(A1,6)
A1 being where your address is and '6' being the number of
character right to left, which would cover most of the
CITY names( choose a number according to your convenience)
*you have the last 'n' characters in that cell.
*Copy ONLY the values onto the same cell or a new cell,
*now run the Text to Column exercise..
Ex: I tried with this data, assuming the last word would
be CITY.
Column A
1 12, Main street, London
2 Road 21,Grand Azur Hotel Residances Suite #5, City2
3 # 21 Indiana, Temple of Doom, City6

After trying, right(A1,8) at column B next to each row,
Column B
1 , London
2 5, City2
3 m, City6

since these Cells contain formulae as well as values,
Copy column B, Paste special only Values back on column B,

Now back to Text to Column Exercise, with Comma and/Or
space as the delimiter,
Column C
1 London
2 City2
3 City6

Anything further, I guess I would leave it to experts
around here to help me out too...!

thanks,
gujjar
 
Back
Top