Removing numbers from the beginning of a text string

N

Night Owl

I have a long list of addresses, some of which start with a house number
then a comma. Is there a (simple) way of finding the position of the comma
within the first (say) 5 characters of the string, and, if it exists, remove
everything to the left of and including the comma, leaving the rest of the
text string?

Thanks in advance,

Pete
 
W

William

Hi Night Owl

Doubtless there are better ways, but if the address is in A2, enter the
following in B2 and copy down.

=IF(ISERROR(FIND(",",LEFT(A2,5),1)),A2,TRIM(RIGHT(A2,LEN(A2)-FIND(",",LEFT(A2,5),1))))

--


XL2003
Regards

William
[email protected]
 
M

Max

Assuming data is in col A, A1 down

Try in B1:
=IF(ISNUMBER(SEARCH(",",TRIM(A1))),TRIM(MID((TRIM(A1)),SEARCH(",",TRIM(A1))+
1,99)),TRIM(A1))
Copy down

The "99" within MID is arbitrary.
Adjust to a higher number if necessary
 
N

Night Owl

Thanks for your replies, guys. I've managed to sort this, now. Thank you.

You wouldn't mind having a look at my next post, would you :)

Peter
 
Top