Software

Question

Gravatar
0 Votes
Locked

Extract text string without knowing start position

By TimFenn ·
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

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

Use SEARCH

by neilb@uk In reply to Extract text string witho ...

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 :)

Back to Software Forum

Start or search

Related Discussions

Related Forums