Is it possible to change and entire worksheet to text?
 

I have these files that I am receiving in Excel that are not set up to be
database friendly. I am spending hours cutting and pasting to rearrange them
into a mode where I can import them into a little Access Database that I have.
The problem is they are full of serial numbers some with symbols and/or
letters some with not. The field in Access is text and so I want to convert
all the information in the Excel files to text rather than a mixture of
values and text. I have been using the =TEXT function but the way the
worksheets are it is just as cumbersome as all the cutting and pasting. I was
hoping for a little macro to just covert everything but in my searches I
cannot find a way. Does anybody know of one?

Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

Hi Carrie,

Addresing the second part of your post:

===========
want to convert
all the information in the Excel files to text rather than a mixture of
values and text. I have been using the =TEXT function but the way the
worksheets are it is just as cumbersome as all the cutting and pasting. I
was
hoping for a little macro to just covert everything but in my searches I
cannot find a way. Does anybody know of one?
===========

Try:

Select all data
Menu | Format | Number | Text



---
Regards.
Norman



"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:848d4b35cfad2@uwe...
I have these files that I am receiving in Excel that are not set up to be
database friendly. I am spending hours cutting and pasting to rearrange
them
into a mode where I can import them into a little Access Database that I
have.
The problem is they are full of serial numbers some with symbols and/or
letters some with not. The field in Access is text and so I want to
convert
all the information in the Excel files to text rather than a mixture of
values and text. I have been using the =TEXT function but the way the
worksheets are it is just as cumbersome as all the cutting and pasting. I
was
hoping for a little macro to just covert everything but in my searches I
cannot find a way. Does anybody know of one?

Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

Thanks Norman -

However I didn't just want to format I needed to change the values to text.
But I think I have solved the issue by inserting a row of text first and then
uploading to Access then it will recognize the entire column as text.

Carie

Norman Jones wrote:
Hi Carrie,

Addresing the second part of your post:

===========
want to convert
all the information in the Excel files to text rather than a mixture of
values and text. I have been using the =TEXT function but the way the
worksheets are it is just as cumbersome as all the cutting and pasting. I
was
hoping for a little macro to just covert everything but in my searches I
cannot find a way. Does anybody know of one?
===========

Try:

Select all data
Menu | Format | Number | Text

---
Regards.
Norman

I have these files that I am receiving in Excel that are not set up to be
database friendly. I am spending hours cutting and pasting to rearrange
[quoted text clipped - 13 lines]
Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

I have never tried it, but it seems like Cells.Value = CStr(Cells.Value)
should work.

"Carrie_Loos via OfficeKB.com" wrote:

Thanks Norman -

However I didn't just want to format I needed to change the values to text.
But I think I have solved the issue by inserting a row of text first and then
uploading to Access then it will recognize the entire column as text.

Carie

Norman Jones wrote:
Hi Carrie,

Addresing the second part of your post:

===========
want to convert
all the information in the Excel files to text rather than a mixture of
values and text. I have been using the =TEXT function but the way the
worksheets are it is just as cumbersome as all the cutting and pasting. I
was
hoping for a little macro to just covert everything but in my searches I
cannot find a way. Does anybody know of one?
===========

Try:

Select all data
Menu | Format | Number | Text

---
Regards.
Norman

I have these files that I am receiving in Excel that are not set up to be
database friendly. I am spending hours cutting and pasting to rearrange
[quoted text clipped - 13 lines]
Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

Hmmmmmm - That sounds interesting, I'll give it a try and let you know.

JLGWhiz wrote:
I have never tried it, but it seems like Cells.Value = CStr(Cells.Value)
should work.

Thanks Norman -

[quoted text clipped - 32 lines]
Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

Select all the cells with values and run this macro

Sub ChangeAllToText()
Dim myC As Range
For Each myC In Selection
myC.Value = "'" & myC.Text
Next myC
End Sub

The line
Cells.Value = CStr(Cells.Value)
won't work because you will lose the formatting - .Text shows the formatted
value, and you need to loop through to put the ' in front of the values,
(especially date values) to keep them from converting back from text.

HTH,
Bernie
MS Excel MVP



"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:848e1685f696f@uwe...
Hmmmmmm - That sounds interesting, I'll give it a try and let you know.

JLGWhiz wrote:
I have never tried it, but it seems like Cells.Value = CStr(Cells.Value)
should work.

Thanks Norman -

[quoted text clipped - 32 lines]
Thanks
Carrie

--
Message posted via http://www.officekb.com

Is it possible to change and entire worksheet to text?
 

Worked like a charm - Thx you guys just saved me many many painful hours!

Carrie

Bernie Deitrick wrote:
Select all the cells with values and run this macro

Sub ChangeAllToText()
Dim myC As Range
For Each myC In Selection
myC.Value = "'" & myC.Text
Next myC
End Sub

The line
Cells.Value = CStr(Cells.Value)
won't work because you will lose the formatting - .Text shows the formatted
value, and you need to loop through to put the ' in front of the values,
(especially date values) to keep them from converting back from text.

HTH,
Bernie
MS Excel MVP

Hmmmmmm - That sounds interesting, I'll give it a try and let you know.

[quoted text clipped - 6 lines]
Thanks
Carrie

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200805/1