I have a column with numbers in them with a leading blank, like below.
'5179455010055341
I want to copy them to another column and have them formatted like below:

5179 4550 1005 5341

I was using Data TextToColumns with a space and ' for delimiters, but I
end up with the last digit of each number being 0, like below.

5179 4550 1005 5340

I am using #### #### #### #### for the formatting and Excel 97. How can
I get the values copied and formatted correctly, without altering the
digits?

TIA

Hi,

First use the formula :
=VALUE(TRIM(A1))
Then use Format Cells Number Custom to input your format #### #### ####
####

HTH
Cheers
Carim

Carim:

I do not understand the steps to take to make this work. When I try to
use =VALUE(TRIM(A1)), I get the following error in cell B1 #VALUE!.

Thanks

Carim wrote:

Hi,

First use the formula :
=VALUE(TRIM(A1))
Then use Format Cells Number Custom to input your format #### #### ####
####

HTH
Cheers
Carim

These appear to be credit card numbers, so the problem is that excel only
supports 15 digits of precision when working with numbers. The reason the
space is there is so they will be treated as text and you won't loose the
last digit. To break them apart use formulas. Assume the first number is in
A2
in B2
=Mid(A2,2,4)
in C2
=Mid(A2,6,4)
in D2
=Mid(A2,10,2)
in E2
=Mid(A2,14,4)

then select B2:E2 and drag fill down the column.

--
Regards,
Tom Ogilvy


"Carim" wrote:

Hi,

First use the formula :
=VALUE(TRIM(A1))
Then use Format Cells Number Custom to input your format #### #### ####
####

HTH
Cheers
Carim