How to write Functions to extract specific text from original

[ac_ric]

Hi Board,

I am a newbie to excel and its function commands. I need help to be able to automatically extract and transfer the extracted information from one cell to another cell. The originating cell is a text string.

Here is a brief:

Cell A2 : 08/15/2011 00:06:10 SITE8 SCOTTS ROAD/ C K TANG ESCALATOR E2 E-STOP. TO ACKNOWLEDGE, REPLY: ACK 128

Cell A3 : 08/15/2011 00:06:10 SITE8 SCOTTS ROAD/ C K TANG ESCALATOR E2 NOT RUNNING

Cell A4: (ID:128) SITE8 SCOTTS ROAD/ C K TANG ESCALATOR E2 E-STOP ACKNOWLEDGED.

Cell A5: 08/15/2011 00:12:47 SITE9 ORCHARD ROAD/ C K TANG ESCALATOR2B E-STOP. TO ACKNOWLEDGE, REPLY: ACK 1451


Cell A7: SITE32 NICOLL HIGHWAY/ ESPLANADE DRIVE & RAFFLES AVE (ART CENTRE) ESCALATOR E1 NOT RUNNING RESET.

I need to be able to to pick out the text e.g "ACK 128" ( as in cell A2) and have it inserted into the column cell B2. However if the cell A2 has no such info, it will insert "NA". Some ACK numbers which appear after the "ACK" may vary between 3 to 4 numbers

For cell C2, I need to be able to pick out the text "SITE" from cell A2 and the number that follows it ( either single digit or double digit"). All cells in column range A will have "SITE"

For cell D2, I need to be able to pick out the word "ACKNOWLEDGED" from the text string ( cell A2). If however there is no such info, a NA will be inserted.

For column E2, I need to be able to pick out the word "RESET"( from cell A2) from the text string . If however there is no such info, a NA will be inserted.

For Column F2, I need to be able to to pick out the text e.g "ESCALATORX OR ESCALATORXX or ESCALATOR XX" and have it inserted into the column. However if the cell has no such info, it will insert "NA". Some ESCLATORS may have 1 digit and 1 alphabet or the word ESCALATOR may not have a space after it.


Can someone guide me on how to get this down. I have a list that runs to 1300 ros daily and it is time consuming to manually extract this info.

Thanks

The file sample is attached

[NBVC]

Try these in B2:F2 and then each copied down.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ac_ric]

Excellent, it work perfectly.

Can't thank you enough for helping me cut down on the unnecesary time spend previously.

Thanks again.