So below are two different text strings. I am trying to get the position of four digit number which is a dynamic number in every text string (8555) to extract the store name (Amazing Stores 2584 or what ever is in that place again dynamic no fixed width so can't use =right).

04/12/2022 13:01:00 00 K 18.30 18.30 USD 926 4 DLKY 1 000 05 8555 0 AMAZING STORES 2584 02/12/2022 13:01:00 00 K 18.30 18.30 USD 926 4 DLKY 1 000 05 8555 0 AMAZING STORES

Now my formula works for the second text string but not for the first. even thought everything is similar with just a change in date (04 instead of 02) and a number after store i.e (Amazing Stores 2584)

Workflow to find the first character of the Four digit number & Store Name

Formula Used : =FIND(LOOKUP(10^15,MID(A2,ROW(INDIRECT("1:"&LEN(A2))),5) 0),A2) 6 in cell b1 Formula Len : =len(a1) in cell c1 Final Result : = right(a1,c1-b1) to extract the store name.

**Few things to note: **

I can't convert text to columns so this option wont work
There is no Fixed length its dynamic as data is dynamic
The Start position cannot be fixed length so can't use mid function either as amount could differ
Text Split not an option as i can only use excel 2016 for this project

I am fairly new to excel so Any help from you experts is greatly appreciated. Thanks.

Tried using multiple formula's and spent close to hours on trying to figure this out on my own, Please help me.

CodePudding user response：

So I would go with either:

FIND(8555,A1,1)

FIND("8555 0",A1,1)

Both return the position of the start of 8555. The first one looks for the number, while the second looks for the number, space, 0. Both return the start position.

Then I would use find() to get the position of the second space, assuming that all store names have a space in them, if not you need to just find the positon of the next space.

As far not being able to use mid(), you can by using find() to define the start position of mid() and also the end position based on finding the position of spaces AFTER "8555 0".

CodePudding user response：

=MID(A1,
     FIND(" "&MATCH(10^15,FIND(TEXT(ROW($1:$9999),"0000"),A1))&" ",A1) 7,
     LEN(A1))

This avoids using the volatile INDIRECT function and finds the position of the first 4 digit number surrounded by a space character and adds 7 to get the start position of the store name. Than it will add all characters from there.