Removing numbers from the beginning of a text string

[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

 

[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
(e-mail address removed)

 

[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

 

[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