ConvertTextToNumbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

alan - 20 Mar 2006 14:42 GMT

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

Reply to this Message

Carim - 20 Mar 2006 14:57 GMT

Hi,

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

HTH
Cheers
Carim

Reply to this Message

alan - 20 Mar 2006 15:46 GMT

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

> Hi,
>
[quoted text clipped - 6 lines]
> Cheers
> Carim

Reply to this Message

Tom Ogilvy - 20 Mar 2006 17:21 GMT

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.

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 6 lines]
> Cheers
> Carim

Reply to this Message