Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip 2 Numbers from a Hyphenated Range
I pulled the following 52-week stock quote range from Yahoo
financials: (8.50 - 11.87). This is in cell A1. My goal would be to have the 8.50 in cell A2 and the 11.87 in cell A3. I tried using the left and right formulas, and they work, but I am trying to avoid a bunch of nested IFs to take into account the many different sizes of numbers I might have to work with. It could range from 3-5 digits on either side. What I am looking for is the formula that would pull the left characters out up until it hits the hyphen, and actually do a minus one to get rid of the space between the digit and the hyphen. Ditto on the right hand side of the range. I appreciate all suggestions. Thanks John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip 2 Numbers from a Hyphenated Range
John,
Try: =TRIM(LEFT(A1,FIND("-",A1)-1)) and =TRIM(MID(A1,FIND("-",A1)+1,99)) -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Johnny" wrote in message ... I pulled the following 52-week stock quote range from Yahoo financials: (8.50 - 11.87). This is in cell A1. My goal would be to have the 8.50 in cell A2 and the 11.87 in cell A3. I tried using the left and right formulas, and they work, but I am trying to avoid a bunch of nested IFs to take into account the many different sizes of numbers I might have to work with. It could range from 3-5 digits on either side. What I am looking for is the formula that would pull the left characters out up until it hits the hyphen, and actually do a minus one to get rid of the space between the digit and the hyphen. Ditto on the right hand side of the range. I appreciate all suggestions. Thanks John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strip 2 Numbers from a Hyphenated Range
On May 20, 2:14*pm, "Chip Pearson" wrote:
Try: =TRIM(LEFT(A1,FIND("-",A1)-1)) and =TRIM(MID(A1,FIND("-",A1)+1,99)) Chip, right on the money. Thanks John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I strip just numbers from an alphanumeric cell? | Excel Worksheet Functions | |||
Sorting hyphenated numbers | Excel Discussion (Misc queries) | |||
sorting hyphenated numbers | Excel Discussion (Misc queries) | |||
how do I preserve hyphenated words | Excel Discussion (Misc queries) | |||
How to strip dashes out of numbers in a variable | Excel Programming |