Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
I'm trying to create a formula that will pick out certain characters within a
text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
assuming "Distributed 1" is common to all strings and there are always 2 $
amounts: For the first $ amount: =LOOKUP(99^99,--MID(A1,16,ROW(INDIRECT("1:255")))) For the second $ amount: =MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,255)+0 You'll have to format to get the decimal 0's back Biff "Peanut" wrote in message ... I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
If there is always " 2" in front of the second value:
=MID(A1,FIND(" 2",A1)+3,255) HTH "Peanut" wrote: I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
Try this:
For a text string with your posted general structure in cell A1 This formula returns the first number in the string: B1: =--MID(LEFT(A1,FIND(" 2 ",A1)-1),FIND("$",A1)+1,255) and this formula returns the second number in the string: C1: =--MID(A1,FIND(" 2 $",A1)+4,255) Copy those formulas down as far as you need. Note: Both returned values are numeric. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Peanut" wrote: I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
This works wonderfully! Thanks for all your help.
"Ron Coderre" wrote: Try this: For a text string with your posted general structure in cell A1 This formula returns the first number in the string: B1: =--MID(LEFT(A1,FIND(" 2 ",A1)-1),FIND("$",A1)+1,255) and this formula returns the second number in the string: C1: =--MID(A1,FIND(" 2 $",A1)+4,255) Copy those formulas down as far as you need. Note: Both returned values are numeric. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Peanut" wrote: I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
This works perfectly. Thanks for your help.
"T. Valko" wrote: assuming "Distributed 1" is common to all strings and there are always 2 $ amounts: For the first $ amount: =LOOKUP(99^99,--MID(A1,16,ROW(INDIRECT("1:255")))) For the second $ amount: =MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,255)+0 You'll have to format to get the decimal 0's back Biff "Peanut" wrote in message ... I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
You're welcome. Thanks for the feedback!
Biff "Peanut" wrote in message ... This works perfectly. Thanks for your help. "T. Valko" wrote: assuming "Distributed 1" is common to all strings and there are always 2 $ amounts: For the first $ amount: =LOOKUP(99^99,--MID(A1,16,ROW(INDIRECT("1:255")))) For the second $ amount: =MID(A1,FIND("$",A1,FIND("$",A1)+1)+1,255)+0 You'll have to format to get the decimal 0's back Biff "Peanut" wrote in message ... I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to Pick Out Characters within a Text String
Another way would be to do Data TextToColumns, and use the $ as the
delimiter........then do Edit Replace space-2 with nothing on column B to get rid of the "2" If you do this frequently, it could be "macroized" Vaya con Dios, Chuck, CABGx3 "Peanut" wrote in message ... I'm trying to create a formula that will pick out certain characters within a text string, but the characters aren't a uniform length, nor are they always the same character number within the string. For example, I would like to pick out the dollar amounts in the following strings. Distributed 1 $91.00 2 $419.00 Distributed 1 $1,980.00 2 $2,519.00 Distributed 1 $94,181.00 2 $42,014.00 I can use the MID function to pick out the first amount, but I can't figure out how to pick out the last amount, since there are a differing number of characters on each line. Let me know if you have any secrets. I would even appreciate a macro that would accomplish the same thing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text String - Specific Characters | Excel Worksheet Functions | |||
Remove characters from a text string using a formula | Excel Discussion (Misc queries) | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
want to remove all text characters equal to one character in length from text string | Excel Worksheet Functions | |||
Insert characters in a text string | Excel Worksheet Functions |