| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Right Formula
Hi ALL,
I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Right Formula
Hi,
This 'probably' works. I say probably because it looks for TT<space and extracts the characters after that so if this seat of characters is repeated in the string it could fail. So put this in B1 and drag down =MID(A1,FIND("TT ",A1,1)+3,LEN(A1)) Mike "alish" wrote: Hi ALL, I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Right Formula
Mike,
Thanks for your response. It did not work. It gave me #VALUE! error. And, Yes, the "TT" will be repeated. Please let me know if you know any other methods. Regards. "Mike H" wrote: Hi, This 'probably' works. I say probably because it looks for TT<space and extracts the characters after that so if this seat of characters is repeated in the string it could fail. So put this in B1 and drag down =MID(A1,FIND("TT ",A1,1)+3,LEN(A1)) Mike "alish" wrote: Hi ALL, I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Right Formula
It worked fine for me in your example data. Is the real data different?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "alish" wrote in message ... Mike, Thanks for your response. It did not work. It gave me #VALUE! error. And, Yes, the "TT" will be repeated. Please let me know if you know any other methods. Regards. "Mike H" wrote: Hi, This 'probably' works. I say probably because it looks for TT<space and extracts the characters after that so if this seat of characters is repeated in the string it could fail. So put this in B1 and drag down =MID(A1,FIND("TT ",A1,1)+3,LEN(A1)) Mike "alish" wrote: Hi ALL, I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Right Formula
Hi,
To have got a #Value! error means that the formula didnn't find the sequence of characters TT<space in A1 which is odd because that sequence appears in every one of your posted examples. With regards to other ideas they would mostly be variations on the same them i.e. find a unique character or sequence of characters and use that to work our where the data you want to extract are. For example your posted examples all have a single "-" character and the data you want are 6 characters to the right of that so this works for every posted example =MID(A1,FIND("-",A1,1)+6,LEN(A1)) Likewise you could you the ) character of which there is only 1 =MID(A1,FIND(")",A1,1)+11,LEN(A1)) But as you will see this thime we are 11 characters from what you want so the risk of error increases. You could also consider text to columns Mike "alish" wrote: Mike, Thanks for your response. It did not work. It gave me #VALUE! error. And, Yes, the "TT" will be repeated. Please let me know if you know any other methods. Regards. "Mike H" wrote: Hi, This 'probably' works. I say probably because it looks for TT<space and extracts the characters after that so if this seat of characters is repeated in the string it could fail. So put this in B1 and drag down =MID(A1,FIND("TT ",A1,1)+3,LEN(A1)) Mike "alish" wrote: Hi ALL, I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Right Formula
Mike,
The second one WORKED! THe first one worked but not in all cases where I have two words in the breckets and in somce cases after the *TT words there was one more word. I used the second formula but within the IF formula. Normally the second word from the *TT is the same repeating word, and so it was easied to use IF formula. THank you ALL. Alish. "Mike H" wrote: Hi, To have got a #Value! error means that the formula didnn't find the sequence of characters TT<space in A1 which is odd because that sequence appears in every one of your posted examples. With regards to other ideas they would mostly be variations on the same them i.e. find a unique character or sequence of characters and use that to work our where the data you want to extract are. For example your posted examples all have a single "-" character and the data you want are 6 characters to the right of that so this works for every posted example =MID(A1,FIND("-",A1,1)+6,LEN(A1)) Likewise you could you the ) character of which there is only 1 =MID(A1,FIND(")",A1,1)+11,LEN(A1)) But as you will see this thime we are 11 characters from what you want so the risk of error increases. You could also consider text to columns Mike "alish" wrote: Mike, Thanks for your response. It did not work. It gave me #VALUE! error. And, Yes, the "TT" will be repeated. Please let me know if you know any other methods. Regards. "Mike H" wrote: Hi, This 'probably' works. I say probably because it looks for TT<space and extracts the characters after that so if this seat of characters is repeated in the string it could fail. So put this in B1 and drag down =MID(A1,FIND("TT ",A1,1)+3,LEN(A1)) Mike "alish" wrote: Hi ALL, I need your help in below issue: I have a column A with below text: (RATIONAL) 4 - STT APPROVED PLUMBER (additional) 1 - STT UNAPPROVED ELECTRICAL SUPERVISOR (RATIONAL) 6 - FTT APPROVED ELECTICIAN (RECOMMENDED) 6 - FTT APPROVED ELECTICIAN Out of that column I want column B with the below results: APPROVED PLUMBER UNAPPROVED ELECTRICAL SUPERVISOR APPROVED ELECTICIAN APPROVED ELECTICIAN What formula do I use to get the result in column Bt? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
