Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi,
I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
If the full text is in A1:
Text2: =MID(A1, 7, FIND("-", A1, 7) -8) Text3: =RIGHT(A1, LEN(A1) -9 -LEN(B1)) This assumes that Text2 is in cell B1. Both formulas assume that: - No dashes exist within Text1. - Text1 is always three characters, and the texts are separated by " - ". David Makelei wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi MakeLei,
Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi,
The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
OK Makelei,
You should be able to do that with Text to Columns wizard. Look it up in help and see how you go. Use the hyphen (-) as the delimiter. Regards, OssieMac "Makelei" wrote: Hi, The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi,
Thanks for you answer, but it does not help me (I know how to use that), since I need to be able to use that data in other formulas... Any proposals? BR MakeLei "OssieMac" wrote: OK Makelei, You should be able to do that with Text to Columns wizard. Look it up in help and see how you go. Use the hyphen (-) as the delimiter. Regards, OssieMac "Makelei" wrote: Hi, The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
=TRIM(MID(A1, FIND("-", A1)+1, FIND("-",A1,FIND("-",A1)+1)-FIND("-", A1)-1))
=TRIM(MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,255)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Makelei" wrote in message ... Hi, Thanks for you answer, but it does not help me (I know how to use that), since I need to be able to use that data in other formulas... Any proposals? BR MakeLei "OssieMac" wrote: OK Makelei, You should be able to do that with Text to Columns wizard. Look it up in help and see how you go. Use the hyphen (-) as the delimiter. Regards, OssieMac "Makelei" wrote: Hi, The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
How does that stop you using the data in other formulae?
-- David Biddulph "Makelei" wrote in message ... Hi, Thanks for you answer, but it does not help me (I know how to use that), since I need to be able to use that data in other formulas... Any proposals? "OssieMac" wrote: OK Makelei, You should be able to do that with Text to Columns wizard. Look it up in help and see how you go. Use the hyphen (-) as the delimiter. "Makelei" wrote: Hi, The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi All,
Thanks for Bob Phillips for the formulae. These are the ones I was looking for. BR Markku "Bob Phillips" wrote: =TRIM(MID(A1, FIND("-", A1)+1, FIND("-",A1,FIND("-",A1)+1)-FIND("-", A1)-1)) =TRIM(MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,255)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Makelei" wrote in message ... Hi, Thanks for you answer, but it does not help me (I know how to use that), since I need to be able to use that data in other formulas... Any proposals? BR MakeLei "OssieMac" wrote: OK Makelei, You should be able to do that with Text to Columns wizard. Look it up in help and see how you go. Use the hyphen (-) as the delimiter. Regards, OssieMac "Makelei" wrote: Hi, The format of the column is as example: "Ext- text2 that might have spaces - Text3 that have spaces" I need to be able to get staright text: "Text2 that might have spaces" and "Text3 that have spaces" BR MakeLei "OssieMac" wrote: Hi MakeLei, Need some confirmation. Is there always a space, hyphen and space between each text string as you have written it. Is it a column of data that you are trying to divide into separate components? If it is then you can use Text to Columns Wizard and use a space as a delimiter (or if the hyphens exist then the hyphens as the delimiters). Lookup Text to columns wizard in Help. If the above does not help then post a sample of the original data and a sample of how you want it to appear. Regards, OssieMac "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi Makelei:
I have in cell C2 the following H01.12345678 I need to extract the 2nd and 3rd character from the left of the decimal into cell G2 Can you help? thank you Arlene "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
If what you mean is get the 2 characters to the left of the decimal,
but there might be more than 3 characters on the left you do this: =MID(C4,FIND(".",C4)-2,2) Basically, it locates the decimal and then uses its position to get the other text. If there will always be the same number of characters you can skip the FIND part: =MID(C4,2,2) On May 22, 12:21 pm, ACarella wrote: Hi Makelei: I have in cell C2 the following H01.12345678 I need to extract the 2nd and 3rd character from the left of the decimal into cell G2 Can you help? thank you Arlene "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Perfect. It worked.
Thank you. Arlene "Reitanos" wrote: If what you mean is get the 2 characters to the left of the decimal, but there might be more than 3 characters on the left you do this: =MID(C4,FIND(".",C4)-2,2) Basically, it locates the decimal and then uses its position to get the other text. If there will always be the same number of characters you can skip the FIND part: =MID(C4,2,2) On May 22, 12:21 pm, ACarella wrote: Hi Makelei: I have in cell C2 the following H01.12345678 I need to extract the 2nd and 3rd character from the left of the decimal into cell G2 Can you help? thank you Arlene "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
I have a similar problem. I have a name in cell A2. It will consist of Title,
Initials, Surname, e.g: Mr A Bloggs Mrs D P Smithers Miss L Young I need to separate the Title, Initials and Surname into 3 columns. Can you suggest how this can be achieved, please? I clearly can't use Text to Columns because the components are of variable length. Thanks. "ACarella" wrote: Perfect. It worked. Thank you. Arlene "Reitanos" wrote: If what you mean is get the 2 characters to the left of the decimal, but there might be more than 3 characters on the left you do this: =MID(C4,FIND(".",C4)-2,2) Basically, it locates the decimal and then uses its position to get the other text. If there will always be the same number of characters you can skip the FIND part: =MID(C4,2,2) On May 22, 12:21 pm, ACarella wrote: Hi Makelei: I have in cell C2 the following H01.12345678 I need to extract the 2nd and 3rd character from the left of the decimal into cell G2 Can you help? thank you Arlene "Makelei" wrote: Hi, I have in cell text as follows: Text1 - Text2 - Text3 I need to get out Text2 in one cell and Text3 to another. What kinf of formula I could use? Lenght of Text2 and Text3 varies, Text1 is always 3 characters. Thanks in advance MakeLei |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
On Fri, 27 Jun 2008 13:28:00 -0700, keithobro
wrote: I have a similar problem. I have a name in cell A2. It will consist of Title, Initials, Surname, e.g: Mr A Bloggs Mrs D P Smithers Miss L Young I need to separate the Title, Initials and Surname into 3 columns. Can you suggest how this can be achieved, please? I clearly can't use Text to Columns because the components are of variable length. Assumptions: 1. Every entry has a title. 2. Every last name is only a single word. A2: Original Name Title: B2: =LEFT(A2,FIND(" ",A2)-1) Initials: C2: =TRIM(MID(A2,LEN(B2)+1,FIND(D2,A2)-FIND(" ",A2)-1)) Last Name: D2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)) --ron |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi Ron
For a moment there, I thought the middle formula wasn't going to work, then, once I'd input the 3rd one, it all fell into place. Marvellous. Can't thank you enough. Keith "Ron Rosenfeld" wrote: On Fri, 27 Jun 2008 13:28:00 -0700, keithobro wrote: I have a similar problem. I have a name in cell A2. It will consist of Title, Initials, Surname, e.g: Mr A Bloggs Mrs D P Smithers Miss L Young I need to separate the Title, Initials and Surname into 3 columns. Can you suggest how this can be achieved, please? I clearly can't use Text to Columns because the components are of variable length. Assumptions: 1. Every entry has a title. 2. Every last name is only a single word. A2: Original Name Title: B2: =LEFT(A2,FIND(" ",A2)-1) Initials: C2: =TRIM(MID(A2,LEN(B2)+1,FIND(D2,A2)-FIND(" ",A2)-1)) Last Name: D2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)) --ron |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
On Sat, 28 Jun 2008 09:54:01 -0700, keithobro
wrote: Hi Ron For a moment there, I thought the middle formula wasn't going to work, then, once I'd input the 3rd one, it all fell into place. Marvellous. Can't thank you enough. Keith You're welcome. Glad to help. Thanks for the feedback. Of course, it is possible to make the "middle formula" stand alone by substituting the formula that is in D2 for the D2 in the middle formula, but I chose not to. A stand-alone formula for the "middle": =MID(A2,FIND(" ",A2)+1,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1), LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2)-1) --ron |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
Hi Ron
We've put the formulae you gave into place and have since identfied one problem. It's OK at identifying all the initials that occur between title and surname, but it can't cope with people whose surname consists of 2 words or more, that are not hyphenated, e.g.: van Bommel le Clerc van den Bosch de la Rue Is there any way we can tweak the formula for the initials to recognise strings as opposed to indiviudal initials? When it finds, for example, van Bommel, it treats that surname as if 1 word. Hope that all makes sense! Thanks. Keith "Ron Rosenfeld" wrote: On Sat, 28 Jun 2008 09:54:01 -0700, keithobro wrote: Hi Ron For a moment there, I thought the middle formula wasn't going to work, then, once I'd input the 3rd one, it all fell into place. Marvellous. Can't thank you enough. Keith You're welcome. Glad to help. Thanks for the feedback. Of course, it is possible to make the "middle formula" stand alone by substituting the formula that is in D2 for the D2 in the middle formula, but I chose not to. A stand-alone formula for the "middle": =MID(A2,FIND(" ",A2)+1,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1), LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-FIND(" ",A2)-1) --ron |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract text from middle of a string
On Wed, 9 Jul 2008 06:25:00 -0700, bollard
wrote: Hi Ron We've put the formulae you gave into place and have since identfied one problem. It's OK at identifying all the initials that occur between title and surname, but it can't cope with people whose surname consists of 2 words or more, that are not hyphenated, e.g.: van Bommel le Clerc van den Bosch de la Rue Is there any way we can tweak the formula for the initials to recognise strings as opposed to indiviudal initials? When it finds, for example, van Bommel, it treats that surname as if 1 word. Hope that all makes sense! Thanks. Keith With this level of complexity, I would use VBA functions. But we would need to know the entire range of possible data in order to do this. For example, if it is the case that the format is always: <title <0-n initials <1-n strings of at least 2 letters this can be easily done. Let me know if this is the case, or if there is more variation. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add a character to the middle of a text string | Excel Discussion (Misc queries) | |||
Extract text from a string | Excel Worksheet Functions | |||
Extract text string using MID | Excel Worksheet Functions | |||
extract " " space from middle of a string | Excel Worksheet Functions | |||
Extract text from String | Excel Worksheet Functions |