Splitting Apart Data in a cell

[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

[HansV]

This should do it:

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

[Leesha]

Perfect as usual! Thanks so much!

[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}})