Help on conversion

  • Thread starter Thread starter Help on formula
  • Start date Start date
H

Help on formula

Hi

In Column-A there is a list of numbers with hyphen i.e.:
95818-125199

How can this list of numbers covert into following format in Column-B?
95818-1251-99

In other words of explanation is that I want to keep this format
95818-125199 in Column-A, but to change the format into 95818-1251-99 in
Column-B. Please help me with Formula or Macro so each time when I enter
95818-125199 in Column-A it can convert automatically in the format of
95818-1251-99 in Column-B. I thank you in advance.
 
In cell B1 try something like...

=LEFT(A1,LEN(A1)-2) & "-" & RIGHT(A1,2)

copy it down the column as you need, if you don't want to see #VALUE errors
for rows with no value in column A, simply add a condition check for the cell
in the A column, perhaps like this...

=IF(A1<>0,LEFT(A1,LEN(A1)-2) & "-" & RIGHT(A1,2),0)
 
Assuming all your numbers are 13 characters long, try this formula...

=LEFT(A1,10)&"-"&RIGHT(A1,2)

If you will have empty cells in column A, the use this version...

=IF(A1="","",LEFT(A1,10)&"-"&RIGHT(A1,2))
 
Thank you very much from the deepest of my hart!
Mr. Peter and Mr. Rothstein your formula feed-back works great; Thank again
 
Back
Top