Question Locked

Extract text string without knowing start position

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to extract a text string form a memo field that we imported into Excel, I have used the Find and Mid function but only works if your finding a single Char, I need to extract mobile number that begin with 078,077 and 075 the next number of char's is 11

2 replies

by Member 25348569

that will let you look for "07"

A1 = "phone 07781233456 and some more crap"

=MID(A1,SEARCH("07",A1),11)

returns 07781233456

If you want to search for ONLY 078, 077 and 075 you're going to have to nest a few IFs.

If there isn't a phone number in the line, you'll get a !#VALUE error unless you use something like

=IFERROR(MID(A1,SEARCH("07",A1),11),"")

Neil :)

This discussion is closed to new replies.