Removing numbers from cell

[jacques]

Hi

I use SharePoint quite frequently and sometimes need to display the data in an Excel dashboard. By the way, Chandoo, thanks for all the dashboard tips!

When I export PeoplePicker fields from SharePoint into Excel the results look like this when multiple people have been picked in the SharePoint list:

Ben Kingsley; #457; David Tenant; #71; Peter O'Toole; #4210

I want to remove all of the unwanted identification values as well as the preceding semicolon, e.g. "; #457" and only want to be left with the names delimited by semicolon:

Ben Kingsley; David Tenant; Peter O'Toole;

This is my current formula with the relevant information in cell A1 (by the way, I have also removed the very last semicolon):

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"9",""),"8",""),"7",""),"6",""),"5",""),"4",""),"3",""),"2",""),"1",""),";#;#","; "),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"9",""),"8",""),"7",""),"6",""),"5",""),"4",""),"3",""),"2",""),"1",""),";#;#","; "))-2)

I am sure there is a better way than using multiple nested substitute functions. I can use VBA, there are several posts on this as well as on UDF's, however I would like to do this using formulas only.

Any ideas?

 

[SirJB7]

Hi, jacques!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, give a look at this recent thread:
http://chandoo.org/forum/threads/extract-only-text.14459/#post-86198

Regards!

 

[jacques]

Thanks

 

[SirJB7]

Hi, jacques!
Glad you help you, even if it wouldn't solve your issue, I guess. Welcome back whenever needed or wanted.
Regards!

 

[István Hirsch]

If a formula is not a must, just select the cells to process then replace _#*; with nothing then _#* with nothing.

 

[shrivallabha]

You could cut down some SUBSTITUTE portion for sure. Following should give the same result.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"9",""),"8",""),"7",""),"6",""),"5",""),"4",""),"3",""),"2",""),"1",""),"; #;",","),"; #","")