I have some numbers in column G that are formatted as general but newly
pasted text into the column doesnt seem to pick up the destination
formatting, even after formatting the selection. (I know this because
it distinguishes between it in a pivot table). It seems I need to go
through each cell and double click at the end of the text. Is there are
way to do this programatically? I figured if I recorded a macro doing
it in one cell then amending the code to go through the column? I've
never used a loop before so would appreciate any advice. The range of
cells is called SPECIALTY. TIA -Ciara

Select the cells to process, then run the macro.

Sub correctData()
for each cell in Select
cell.Numberformat = "General"
cell.Value = Replace(Trim(cell.Value),chr(160),"")
Next
End sub

--
Regards,
Tom Ogilvy


"Ciara" wrote:

I have some numbers in column G that are formatted as general but newly
pasted text into the column doesnt seem to pick up the destination
formatting, even after formatting the selection. (I know this because
it distinguishes between it in a pivot table). It seems I need to go
through each cell and double click at the end of the text. Is there are
way to do this programatically? I figured if I recorded a macro doing
it in one cell then amending the code to go through the column? I've
never used a loop before so would appreciate any advice. The range of
cells is called SPECIALTY. TIA -Ciara

this worked perfectly...
I changed "SELECT" to "Selection" as I think SELECT is one of the
reserved words... anyway it worked. Thanks Tom
Tom Ogilvy wrote:
Select the cells to process, then run the macro.

Sub correctData()
for each cell in Select
cell.Numberformat = "General"
cell.Value = Replace(Trim(cell.Value),chr(160),"")
Next
End sub

--
Regards,
Tom Ogilvy


"Ciara" wrote:

I have some numbers in column G that are formatted as general but newly
pasted text into the column doesnt seem to pick up the destination
formatting, even after formatting the selection. (I know this because
it distinguishes between it in a pivot table). It seems I need to go
through each cell and double click at the end of the text. Is there are
way to do this programatically? I figured if I recorded a macro doing
it in one cell then amending the code to go through the column? I've
never used a loop before so would appreciate any advice. The range of
cells is called SPECIALTY. TIA -Ciara

Yes, it should have been selection. My typo. Glad you were able to move it
to a solution.

--
Regards,
Tom Ogilvy


"Ciara" wrote:

this worked perfectly...
I changed "SELECT" to "Selection" as I think SELECT is one of the
reserved words... anyway it worked. Thanks Tom
Tom Ogilvy wrote:
Select the cells to process, then run the macro.

Sub correctData()
for each cell in Select
cell.Numberformat = "General"
cell.Value = Replace(Trim(cell.Value),chr(160),"")
Next
End sub

--
Regards,
Tom Ogilvy


"Ciara" wrote:

I have some numbers in column G that are formatted as general but newly
pasted text into the column doesnt seem to pick up the destination
formatting, even after formatting the selection. (I know this because
it distinguishes between it in a pivot table). It seems I need to go
through each cell and double click at the end of the text. Is there are
way to do this programatically? I figured if I recorded a macro doing
it in one cell then amending the code to go through the column? I've
never used a loop before so would appreciate any advice. The range of
cells is called SPECIALTY. TIA -Ciara