Extract 8 digit number from a string

Hans K

New Member
Joined
Oct 30, 2015
Messages
43
Office Version
  1. 365
Platform
  1. Windows
I found a useful formula here

Finding numbers in text string [SOLVED] (daddylonglegs) with the following formula:

=TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-7)),8)+0),"00000000")

I tried to change this formula to =TEXT(LOOKUP(10^8,MID(SUBSTITUTE(A1," ","x"),ROW(INDIRECT("1:"&LEN(A1)-5)),6)+0),"00000000") to extract a 6 digit number and it seems to work fine, but I have a hard time trying to understand the ROW(INDIRECT("1:"&LEN(A1)-7)),8) part of the formula versus ROW(INDIRECT("1:"&LEN(A1)-5)),6). Someone care to help me understand?
 
@Rick Rothstein
Thank you.

Rknr. 1. SENDES via lokal 2354 med FTT og 42
Rknr. 2. SENDES 210523 til TFT med 23210523
Rknr. 3. SENDES 22042023 med FTT på 32422042023
Rknr. 4. SENDES via lok 456 med TTF den 050423050423
Rknr. 5. SENDES via lok 6 og 456 med TTF den 0404202304042023
Rknr. 6. SENDES med 009 og 55 til FTF

The above shows some sample data and the expected results. Please note that I haven't earlier mentioned anything about 6 digit numbers but those I also want returned. Yor formula returns

1. SEN
2. SEN
3. SEN
4. SEN
5. SEN
6. SEN
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
@Rick Rothstein
Thank you.

Rknr. 1. SENDES via lokal 2354 med FTT og 42
Rknr. 2. SENDES 210523 til TFT med 23210523
Rknr. 3. SENDES 22042023 med FTT på 32422042023
Rknr. 4. SENDES via lok 456 med TTF den 050423050423
Rknr. 5. SENDES via lok 6 og 456 med TTF den 0404202304042023
Rknr. 6. SENDES med 009 og 55 til FTF

The above shows some sample data and the expected results. Please note that I haven't earlier mentioned anything about 6 digit numbers but those I also want returned. Yor formula returns

1. SEN
2. SEN
3. SEN
4. SEN
5. SEN
6. SEN
My formula returns that because your original post did not give us a clear picture of your actual data or your actual requirement. Here is something that may help you for future questions you may ask in this (or any) forum...

Please Note
-------------------
For future questions you may ask, please do not simplify your question for us... doing so will get you a great answer to a question you do not actually have and which you do not actually care about AND it will almost always lead to you coming back for help when the solution we give you for the simplified question cannot be applied to your actual data and its layout. One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.
 
Upvote 0
I really regret.
I asked a question and I immediately got an answer from bebo021999 which solved what I was asking about and I immediately marked that post as a solution. I hadn't expected anything more than that.
 
Upvote 0
Here's another option for the latest sample/expected results:

SQL script 1 - 2023-06-23T091608.236.csv
ABC
1Rknr. 1. SENDES via lokal 2354 med FTT og 42 
2Rknr. 2. SENDES 210523 til TFT med 23210523
3Rknr. 3. SENDES 22042023 med FTT på 32422042023
4Rknr. 4. SENDES via lok 456 med TTF den 050423050423
5Rknr. 5. SENDES via lok 6 og 456 med TTF den 0404202304042023
6Rknr. 6. SENDES med 009 og 55 til FTF 
7Rknr. 5. SENDES via lok 6 og 456 med TTF den 04042023 DSA 12345604042023123456
Sheet1
Cell Formulas
RangeFormula
B1:B6,B7:C7B1=LET(TS,TEXTSPLIT(A1," "),FILTER(TS,((LEN(TS)=6)+(LEN(TS)=8))*ISNUMBER(TS+0),""))
 
Upvote 0
I can't do anything more than say thank you as I already have marked another post as a solution.
Your formula is perfect and easy to understand, but I will emphasize that my original question was about understanding a part of a already working formula and bebo021999 helped me understand.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,161
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top