Split value into 2 cells
 

I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric)
goes into cell A, and cell B will only retain 234 (numeric). What is the best
way to accomplish this? I will be doing this every day, so the most time
saving solution will be greatly appreciated. Thank you.

Split value into 2 cells
 

If you have 1067-234 in B1 then in A1 use
a) if the hyphen is always in the fifth position, =--LEFT(B1,4)
b) if the hyphen could be anywhere =--MID(B1,1,FIND("-",B1)-1)
Note in both cases the double negation is used to convert text to number

A formula cannot alter another cell so we cannot alter B1
Let's insert a new column B so that 1067-234 is in C1
In B1 use
a) if the hyphen is always in the fifth position and foloowed by 3 digits,
=--MID(C1,6,3)
b) if the hyphen could be anywhere =--MID(C1,FIND("-",C1)+1,256)

Select A1:B1 (or A1:B100) if you are working with 100 rows of data) ; use
the Copy command followed by Edit | Paste Special with Values specified.
The formulas will be converted to values and the original data in column C
can be deleted

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Vic" wrote in message
...
I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric)
goes into cell A, and cell B will only retain 234 (numeric). What is the
best
way to accomplish this? I will be doing this every day, so the most time
saving solution will be greatly appreciated. Thank you.

Split value into 2 cells
 

Select B1 and pull-down:

Data Text to Columns

specifiy the - as the separator and make A1 the destination.
--
Gary''s Student - gsnu200859


"Vic" wrote:

I need to split the value of cell B 1067-234 into 2 cells. 1067 (numeric)
goes into cell A, and cell B will only retain 234 (numeric). What is the best
way to accomplish this? I will be doing this every day, so the most time
saving solution will be greatly appreciated. Thank you.