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

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

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

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

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

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

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