Extract a word starts with a list of characters from a string

Hi

Would like to seek your assistance to extract a word from the raw data starts with "AMS, AMBG, MYDR, IB2K, MYPR...." and could be more.

Raw data Result
Primary:AMBG2Kabc:NT AMBG2Kabc
MYDRVLXRAUTHNab:KUL MYDRVLXRAUTHNab
IBTRBSQL:IB2KAAB:MSS IB2KAAB
IB2KBB:UA IB2KBB
RZ:MTP-MTP-ambgsun99:RDB ambgsun99
AMBG2K999:AMBG2K999:MSS AMBG2K999
AMS:KUL AMS


Thank you.

Please post a sample showing expected outcomes.

Hi,
My apology, when I paste it, did not display correctly.

Primary:AMBG2Kabc:NT
Extract should be AMBG2Kabc

MYDRVLXRAUTHNab:KUL
Extract should be MYDRVLXRAUTHNab

IBTRBSQL:IB2KAAB:MSS
Extract should be IB2KAAB

IB2KBB:UA
Extract should be IB2KBB

RZ:MTP-MTP-ambgsun99:RDB
Extract should be ambgsun99

AMBG2K999:AMBG2K999:MSS
Extract should be AMBG2K999

AMS:KUL
Extract should be AMS

ip.pipe:#99.9.999.99[2026]<NM>AMBG2K999DR</NM>
Extract should be 99.9.999.99

File required as people do not wish to type in your data.

ip.pipe:#99.9.999.99[2026]<NM>AMBG2K999DR</NM>
Extract should be 99.9.999.99

Is this correct? Not AMBG2K999DR

Hi,
Yes, it's 2 different formulas.

Still no file or explanation why last example is required result.

2 different formulas ... what are they?

Hi,

The 1st formula is would like to extract the hostname based on the list first few characters based on the list of AMS, AMBG, MYDR, IB2K, MYPR...." and could be more.
It could be any where in the string.

The 2nd formula, would like to extract the ip address only after the # before the [.

Attached is the sample data. Please assist as we need to extract this everyday and massage the data using text to columns which is time consuming.

Thank you.

Looking at your file you are going to need a VBA-based solution. In order to do this, we need a list of all possible strings such as "AMS, AMBG .." etc.

Doing extracts like these are not easy and are usually not 100% effective.

For Ip you could use

=MID(C3,FIND("#",C3)+1,FIND("[",C3)-FIND("#",C3)-1)

Hi,

Thank you for the response.
The strings need to extract are AMS, AMAB, MYPV, MYDR, MYDV, EMAIL, AMS, AMBG, IB2K, MYPP, AMF, EXT, MCP, RENT, sa10

Try the following:

Please Login or Register  to view this content.

Sheet2 has named ranges called "Codes".

Ip is extracted using formula (in D3)

for first formula, put on B3 and copied down

=TRIM(RIGHT(SUBSTITUTE(TRIM(TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A3),":",REPT(" ",60)),120),60))),"-",REPT(" ",255)),255))

Hi Azumi,

Let say, if I have this ip.pipe:#11.9.9.99[12345]<NM>AMBG2K123DR</NM>. I need to extract AMBG2K123DR only.

All the data has <NM> at front of hostname and </NM>after the hostname.

Need your assistance to have the formula to extract the hostname only. Thank you.

Try this: wonderful system will not allow me to post the code but it only a minor change to the previous.

The formula is working beautifully. Thank you all for your assistance.