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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes that is correct.
-
What is the longest string you anticipate?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
this will do the trick, Thanks for the help!
-
Happy to help! 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!