Hi,

I'm looking for a formula which helps me to extract text from a text string.

ie:

GBGD.6251.700001.netpay
GBGD.6251.700001.paye
GBGD.6251.700001.NI
GBGD.6251.700008.pension contribution
GBGD.6251.700020.EER
GBGD.6251.700021.EEE
And I'd like to extract only 700001,700008,700020& 700021.
I've looked at MID,LEN ect but not sure how i can use those formula for this purpose.

Can someone help me?
I also attached the Excel example.



Many thanks

Attached Files

Book1.xlsx (8.7 KB, 14 views)

With your data in A2:B7 enter in C2:

=--MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,F IND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)

Copy down.

Or
Data
Text to columns
Fixed width
Insert break lines
In the last window select "Do not import" for the unnecessary columns

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Or

=TRIM(LEFT(RIGHT(SUBSTITUTE(D4,".",REPT(" ",100)),200),100))

There's more than one way to skin a cat

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

All the more reason they have nine lives....

This works the wonder!!!

Just wondering why "--"?

Many thanks

It's called a double unary and it's used to convert true/false to 1/0

This may be a good place to start with an explanation...

Double Unary

Thank you for this. I tried but dont think it will work as will delete the original data

Many thanks