Substitute

M

mrbalaje

Suppose if I have "1-2-3-4-5" or "12-1-34-56" in a cell. If there any formula
to retrieve only the number without the "-" in another cell.
 
M

Max

If you mean "1-2-3-4-5" in A1 and you want the real number: 12345 in B1
then place in B1: =SUBSTITUTE(A1,"-","")+0
Copy B1 down to return correspondingly if required

If you mean separate the "1-2-3-4-5" in A1 into 5 cols: 1,2,3,4,5
select col A, then click Data > Text to column > Delimited
In step 2, Type a dash: - in the "Other" box, click Finish
(ensure there's no data in cols B to E before you do the above)
 
M

MartinW

And one more. To change them in place use
find and replace
Find What: -
Replace With: leave box blank
Click Replace All

HTH
Martin
 
Top