Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 698
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR CLR is offline
external usenet poster
 
Posts: 594
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text String - Specific Characters Kiser Excel Worksheet Functions 6 February 10th 06 03:43 AM
Remove characters from a text string using a formula duncrbrt Excel Discussion (Misc queries) 1 June 4th 05 02:19 AM
want to remove all text characters equal to one character in length from text string [email protected] Excel Worksheet Functions 1 April 18th 05 09:56 PM
want to remove all text characters equal to one character in length from text string [email protected] Excel Worksheet Functions 1 April 18th 05 12:25 AM
Insert characters in a text string jamae918 Excel Worksheet Functions 1 March 28th 05 10:04 PM


All times are GMT +1. The time now is 11:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"