Getting a base number out of a list

RossL

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?

Paul Newcome

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

Paul Newcome

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?

RossL

@Paul Newcome

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

Paul Newcome

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.

RossL

Yes that is correct.

Paul Newcome

What is the longest string you anticipate?

RossL

the longest string i would anticipate would be 10 digits.

Paul Newcome

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

RossL

@Paul Newcome

this will do the trick, Thanks for the help!

Paul Newcome

Happy to help! 👍️