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
Splitting Apart Data in a cell
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Apart Data in a cell
This should do it:
=IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C7,"#","")," ",REPT(" ",255)),255))),"")
=IFERROR(VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C7,"#","")," ",REPT(" ",255)),255))),"")
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Splitting Apart Data in a cell
Perfect as usual! Thanks so much!
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Splitting Apart Data in a cell
With Power Query you can try with Text.Select()
eg.
eg.
Code: Select all
=Table.TransformColumnTypes(Table.AddColumn(Source, "Custom", each Text.Select([Column1],{"0".."9"})),{{"Custom", type number}})