Splitting Apart Data in a cell

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Splitting Apart Data in a cell

Post by Leesha »

Hi!
I have a formula that I use to get the numbers out of data in cell C. The data comes in in this format:
XXX-XXXX #3580 and I use this formula to separate out the numbers
IFERROR(ROUND(VALUE(MID(C7,SEARCH("#",C7)+1,LEN(C7))),2),"")

This has worked fine for years. Now the data is coming with a mixture of data formatted as above and also formatter as XXX-XXXX 3580. Note, the numbers change as they identify stores. The x's are not the actual words. I just put this in for confidentiality purposes. The hyphen is always there. What would the formula look like to account for both scenerios?

Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Splitting Apart Data in a cell

Post by HansV »

This should do it:

=IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C7,"#","")," ",REPT(" ",255)),255))),"")
Regards,
Hans

Leesha
5StarLounger
Posts: 1112
Joined: 05 Feb 2010, 22:25

Re: Splitting Apart Data in a cell

Post by Leesha »

Perfect as usual! Thanks so much!

hamster
NewLounger
Posts: 7
Joined: 10 Mar 2021, 22:57

Re: Splitting Apart Data in a cell

Post by hamster »

With Power Query you can try with Text.Select()
eg.

Code: Select all

=Table.TransformColumnTypes(Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9"})),{{"Custom", type number}})