Clean cell to just show text

[dgavin]

Is there a formula that will cut any numbers or symbols out of a cell

i.e

Joe Bloggs (01-56-90)

i just want Joe Bloggs

Mr Smith017789

I just want Mr Smith

Many Thanks

 

[GlennUK]

And what about:
Tom Smyth-Jones
?
You'd want that kept as it is, wouldn't you?

 

[dgavin]

luckily I have no hyphens in any names do it shouldn't matter

Thanks

 

[GlennUK]

OK, that's good. Your 2 examples are very similar in that the unwanted characters are on the end of you names ... is that the case for all your data?

 

[dgavin]

yes, all numbers and symbols are after the names

Thanks

 

[GlennUK]

Here's one way, with an array formula, entered using Ctrl-Shift-Enter:

=LEFT(A2,MIN(IF(CODE(MID(SUBSTITUTE(A2," ","^"),ROW(INDIRECT("$A$1:A"&LEN(A2))),1))>64,999,ROW(INDIRECT("$A$1:A"&LEN(A2)))))-1)

... although I'm sure someone will come up with something a little simpler.

 

[bosco_yip]

pls ignore of my reply

 

[bosco_yip]

Or this shorter array formula :

{=LEFT(A1,MATCH(1,N(INDIRECT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)&65536))))}

Regards

 

[bosco_yip]

And, this non-array formula :

=LEFT(A1,LOOKUP(2,1/(ABS(CODE(MID(UPPER(A1),ROW($1:$1024),1))-77.5)<13),ROW($1:$1024)))

Regards

 

[GlennUK]

And, this non-array formula :

=LEFT(A1,LOOKUP(2,1/(ABS(CODE(MID(UPPER(A1),ROW($1:$1024),1))-77.5)<13),ROW($1:$1024)))

Regards

I like that one!