Deleting parts of data

  • Thread starter Thread starter ifew552
  • Start date Start date
I

ifew552

On my data sheet, I have 5#-4#-2#(on colume B only Ex 12345-6789-01)
was hoping to find a way to delete hypens and last 2#. It would tak
me a while to erase this manually. thanks for your help!
 
Hi
if this is stored as 'Text (and is not a custom format) try the
following formula
=SUBSTITUTE(LEFT(A1,LEN(A1)-2),"-","")

and if you want this stored as a number use
=--SUBSTITUTE(LEFT(A1,LEN(A1)-2),"-","")
 
hi, i typed in exactly as i you put it but did not work. Please keep i
mind this data is in colum b and it has 786 rows. thanks
 
Hi
you have to change the cell references. Put the following in cell C1:
=SUBSTITUTE(LEFT(B1,LEN(B1)-2),"-","")
and copy this down for all rows
 
What happens Frank, if the data doesn't start in row1?
You might get another message stating it still doesn't work correctly.<bg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi
you have to change the cell references. Put the following in cell C1:
=SUBSTITUTE(LEFT(B1,LEN(B1)-2),"-","")
and copy this down for all rows
 
Hi RD
we will see <vbg>
but maybe the OP sees the formula logic and adapts on his own :-)
 
frank, thank you for your help! I got it to work for cell 1 but now
need to do this for all of 786 rows. so please pass on your wisdom.
once again thanks for your help
 
Back
Top