| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim left of a found word in a text variable
Mouthful ain't it!
Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
=MID(A1,SEARCH("Trf To",A1),LEN(A1))
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
Thanks Mate
"Ken Wright" wrote: =MID(A1,SEARCH("Trf To",A1),LEN(A1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
You're welcome :-)
"MervB" wrote in message ... Thanks Mate "Ken Wright" wrote: =MID(A1,SEARCH("Trf To",A1),LEN(A1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
Ken,
Slight problem - my excel cant locate the search function MervB :-) "Ken Wright" wrote: You're welcome :-) "MervB" wrote in message ... Thanks Mate "Ken Wright" wrote: =MID(A1,SEARCH("Trf To",A1),LEN(A1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
Ken,
Based on your input, I believe I've solved it: Dim MyString As String MyString = Range("D5") ' MyString = "Transfer the income trf to kim books 26 oct" MyString = Mid(MyString, InStr(MyString, "trf to"), Len(MyString)) MsgBox (MyString) Thanks :-) "MervB" wrote: Ken, Slight problem - my excel cant locate the search function MervB :-) "Ken Wright" wrote: You're welcome :-) "MervB" wrote in message ... Thanks Mate "Ken Wright" wrote: =MID(A1,SEARCH("Trf To",A1),LEN(A1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------Â*------------------------------Â*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------Â*------------------------------Â*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
trim left of a found word in a text variable
Sorry, didn't realise you were using VBA. I know it's the Programming
group, but with the data given the answer was available without the use of VBA. Glad you got sorted though :-) Regards Ken................. "MervB" wrote in message ... Ken, Based on your input, I believe I've solved it: Dim MyString As String MyString = Range("D5") ' MyString = "Transfer the income trf to kim books 26 oct" MyString = Mid(MyString, InStr(MyString, "trf to"), Len(MyString)) MsgBox (MyString) Thanks :-) "MervB" wrote: Ken, Slight problem - my excel cant locate the search function MervB :-) "Ken Wright" wrote: You're welcome :-) "MervB" wrote in message ... Thanks Mate "Ken Wright" wrote: =MID(A1,SEARCH("Trf To",A1),LEN(A1)) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "MervB" wrote in message ... Mouthful ain't it! Basically I have a text variable which can be of any length but includes the phrase "Trf To". I want to modify the text by trimming all character to the left of the phrase. MervB :-) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| trim everything from left of first underscore in the text? | Excel Worksheet Functions | |||
| TRIM, LEFT, RIGHT | Excel Worksheet Functions | |||
| left trim | Excel Worksheet Functions | |||
| Trim Function - Error Library Not Found | Excel Programming | |||
| Trim, Len, Left causing problem | Excel Programming | |||
Linear Mode
