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)


--
Regards - Peter


"Help on formula" wrote:

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.

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))

--
Rick (MVP - Excel)


"Help on formula" wrote in message
...
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.

Thank you very much from the deepest of my hart!
Mr. Peter and Mr. Rothstein your formula feed-back works great; Thank again


"Help on formula" wrote:

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.