20/11/2022 12:00:52 2 X 15.95 15.95 USD 57 5 689 5 689 1 4111 0 Amazing Lego Team

I need to get the position of No 4111 in the above text string, As an excel beginner any help will be greatly appreciated. Thanks.

All of the Text Strings will have a 4 digit number like 4111 which i have to get the position for.

Have tried using this formula to get four digit number in another column, LOOKUP(10^15,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5) 0) but I am looking to get position instead.

I have tried using lookup but I could only go so far as a beginner.

CodePudding user response：

Use the formula you provided =LOOKUP(10^15,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5) 0) to identify the number, and use FIND to get the position of the number.

Cell A1="20/11/2022 12:00:52 2 X 15.95 15.95 USD 57 5 689 5 689 1 4111 0 Amazing Lego Team"

=FIND(LOOKUP(10^15,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),5) 0),A1)
=58

CodePudding user response：

This looks to be in a space (or some other character) delimited text string. If you have a bunch of rows of data like this, use the Text to Columns feature on the Data tab. Use the Delimited option and then click the check box next to space (or other if it's something custom not in the available options) and Excel will split the data into columns for you.

CodePudding user response：

For your string example, if you know that your 4 digit is started 58 char from the beginning of the string, use =MID(A1,58,4) A1 is the cell with your string.

MID function returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

CodePudding user response：

=LET(t,TEXTSPLIT(A1,," "),
FIND(FILTER(t,(LEN(t)=4)*(ISNUMBER(--(t)))),A1))

It splits the string at every space and filters the result for being of length of 4 characters and not being an error if converted to number.