Getting a base number out of a list
Hi I could use some help with this list of numbers.
what i am looking to do is come up with a formula that will return 1234 no mater how it is entered. 1234 is just an example number but it could be 52368 or 65875 but it will always be a numeric value needs to be returned. it could have any number of letters or leading zeros in front of it and any number of letters after that would need to be stripped away. Is there a way to do this?
Best Answer

Ok. I am going to suggest ten helper columns and an optional helper row. I named each of my helper columns by the number from left to right "One", "Two", "Three", "Four", so on and so forth to "Ten".
In the helper row (I used row 1 but you can use whichever row you want), I entered those same numbers so 1, 2, 3, 4, ...........................10.
Then in the cell One2, I entered this:
=IFERROR(VALUE(MID($[Primary Column]@row, One$1, 1)), "")
If you do not want to use the helper row, you would use:
=IFERROR(VALUE(MID($[Primary Column]@row, 1, 1)), "")
and replace that first 1 (in bold above) with 2, 3, 4, etc as you move across the columns.
You can then dragfill over and down to cover the rest of the helper columns and existing rows.
Then to pull the number and drop leading zeros, I used:
=VALUE(JOIN(One@row:Ten@row))
Answers

What is the maximum number of digits in any given string? Will you ALWAYS want to get rid of zeros, or would you want to keep them if they are in the middle of the number you want to pull?

I would only want to get rid of any leading zeros and non numeric digits on both ends of the part number.
here is a sample list of what I have. I would only want the main number Example for the first one in the list below it would be 49986 and not H049986XB. the second would be 902404 and not H902404XA.
H049986XB
H902404XA
46995
45939
052089XK
052428
46995XL
h052147xa
H902397XA
52518
52518
052514s
052515S
049462S
SAMPLE BLANKS
H036679AE
H036679AE
902125
45939
47013
52038

And just to be clear...
052514s would be 52514
H036679AE would be 36679
52038 would be 52038
Are those correct?
I have a few ideas but need to do some testing.

Yes that is correct.

What is the longest string you anticipate?

the longest string i would anticipate would be 10 digits.

Ok. I am going to suggest ten helper columns and an optional helper row. I named each of my helper columns by the number from left to right "One", "Two", "Three", "Four", so on and so forth to "Ten".
In the helper row (I used row 1 but you can use whichever row you want), I entered those same numbers so 1, 2, 3, 4, ...........................10.
Then in the cell One2, I entered this:
=IFERROR(VALUE(MID($[Primary Column]@row, One$1, 1)), "")
If you do not want to use the helper row, you would use:
=IFERROR(VALUE(MID($[Primary Column]@row, 1, 1)), "")
and replace that first 1 (in bold above) with 2, 3, 4, etc as you move across the columns.
You can then dragfill over and down to cover the rest of the helper columns and existing rows.
Then to pull the number and drop leading zeros, I used:
=VALUE(JOIN(One@row:Ten@row))

this will do the trick, Thanks for the help!

Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!