Make Existing Data In Cells Text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

masonap - 25 May 2006 08:21 GMT

I frequently have to use .xls or .csv files to import data into another
system, however the system requires that all fields be text (except
where a number is a genuine number).

My problem is that I have two colums of numbers that have to be
converted to text before I can run the import. Most users get this
right before they submit the files to me for processing, however a few
don't ! ! !

I use the format cells to make the columns text, but then have to press
F2 on every cell. This is not a problem when the file is up to 150
lines, but I have a couple of files where there could be 5,000 lines.

Does anyone know a formula that I can create/use to do this for me?

Thanks

Signature

masonap

Reply to this Message

Gary''s Student - 25 May 2006 09:58 GMT

If you have a column of numbers and you select the entire column and pull-down:
Format > Cells... > Text

all the cells should become text without F2 being needed on each cell

Signature

Gary''s Student

> I frequently have to use .xls or .csv files to import data into another
> system, however the system requires that all fields be text (except
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message

masonap - 25 May 2006 10:27 GMT

I have read the reply from Gary's student.

This does not solve the problem, I still need to F2 on every cell in
order to see the small green triange in the top left corner of the cell
which confirms that the cell is formatted as text correctly.

Signature

masonap

Reply to this Message

Gary''s Student - 25 May 2006 11:19 GMT

If my original suggestion did not work then:

Tools > Options > Error checking > and clear the number stored as text
checkbox

Signature

Gary's Student

> I have read the reply from Gary's student.
>
> This does not solve the problem, I still need to F2 on every cell in
> order to see the small green triange in the top left corner of the cell
> which confirms that the cell is formatted as text correctly.

Reply to this Message

Arvi Laanemets - 25 May 2006 10:19 GMT

Hi

The only foolproof method I'm aware of is a formula like
="" & A1
, or
=TEXT(A1,"#.00")

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> I frequently have to use .xls or .csv files to import data into another
> system, however the system requires that all fields be text (except
[quoted text clipped - 12 lines]
>
> Thanks

Reply to this Message