Removing numbers from the beginning of a text string
 

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

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



"Night Owl" wrote in message
...
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

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Night Owl" wrote in message
...
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

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

"Night Owl" wrote in message
...
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