Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
I have the following in cells
D3: -2.5 -111 D4: +2.5 +101 D5: +10.5 -114 D6: -10.5 +104 D7: -8 +100 D8: +8 -110 Is there a formula I can enter into cell H3 that will give me just -2.5? Note that all the cells aren't necessarily in the same format (some are X.5 and some are X). Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
This formula
=MID(D3,1,FIND(" ",D3,1)-1) .... looks for the space in between the numbers, and pulls everything to the left of the space. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
Assuming all of your cells follow the same format as Number - Space - Number,
then this will work to extract the first number before the space: =LEFT(D3,FIND(" ",D3,1)-1) HTH, Elkar "Jambruins" wrote: I have the following in cells D3: -2.5 -111 D4: +2.5 +101 D5: +10.5 -114 D6: -10.5 +104 D7: -8 +100 D8: +8 -110 Is there a formula I can enter into cell H3 that will give me just -2.5? Note that all the cells aren't necessarily in the same format (some are X.5 and some are X). Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
Assuming that in cell D3 you have a space after the first number and the cell
is formatted as text, in cell H3 type the following formula: =MID(D3,1,FIND(" ",D#,1)-1) and copy down. Note that there is a single space between the two quotation marks. "Jambruins" wrote: I have the following in cells D3: -2.5 -111 D4: +2.5 +101 D5: +10.5 -114 D6: -10.5 +104 D7: -8 +100 D8: +8 -110 Is there a formula I can enter into cell H3 that will give me just -2.5? Note that all the cells aren't necessarily in the same format (some are X.5 and some are X). Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
thanks to all three of you for the help
"SVC" wrote: Assuming that in cell D3 you have a space after the first number and the cell is formatted as text, in cell H3 type the following formula: =MID(D3,1,FIND(" ",D#,1)-1) and copy down. Note that there is a single space between the two quotation marks. "Jambruins" wrote: I have the following in cells D3: -2.5 -111 D4: +2.5 +101 D5: +10.5 -114 D6: -10.5 +104 D7: -8 +100 D8: +8 -110 Is there a formula I can enter into cell H3 that will give me just -2.5? Note that all the cells aren't necessarily in the same format (some are X.5 and some are X). Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
how would I change the formula to pull everything to the right of the space?
"Dave O" wrote: This formula =MID(D3,1,FIND(" ",D3,1)-1) .... looks for the space in between the numbers, and pulls everything to the left of the space. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
One way:
=MID(D3,FIND(" ",D3)+1,100) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jambruins" wrote in message ... how would I change the formula to pull everything to the right of the space? "Dave O" wrote: This formula =MID(D3,1,FIND(" ",D3,1)-1) .... looks for the space in between the numbers, and pulls everything to the left of the space. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split text without using data-text to columns
...Split text ...
If D3 does in fact have "Text", then a possible alternative... =IMREAL(D3& "j") HTH. :) -- Dana DeLouis Win XP & Office 2003 "Jambruins" wrote in message ... I have the following in cells D3: -2.5 -111 D4: +2.5 +101 D5: +10.5 -114 D6: -10.5 +104 D7: -8 +100 D8: +8 -110 Is there a formula I can enter into cell H3 that will give me just -2.5? Note that all the cells aren't necessarily in the same format (some are X.5 and some are X). Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show text data in excel pivot table | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How to convert columns of data to one column of text | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |