Apostrophes and formatting numbers reading as text

  • Thread starter Thread starter Michele
  • Start date Start date
M

Michele

We brought over tons of information from PeopleSoft into
an excel spreadsheet for reporting purposes. However,
several columns with numbers came across as '5425 instead
of 5425. End result is I cannot format these columns to
resemble $5,425. As we are talking about 6 columns with
over 2500 I can't really go in by hand to remove these.

Any suggestions would be greatly appreciated...

Thank you!

Michele
p.s. I've already tried special pasting.
 
Michele, have you tried special pasting like this:

1-put a "1" in an empty cell
2-Copy it
3-Select the cells to paste it to
4-Edit > Paste Special > Multiply > OK.
 
Michele,

Select all the cells that you need to convert and run this macro:

Sub Transform2()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP
 
Hi
try the following macro:
sub convert_it()
dim rng as range
set rng = activesheet.range("A1:A1000")
rng.value=rng.value
end sub
 
Hi

Try copying an empty cell. Then select your range and Edit / Paste Special .
.. Add
This usually does the trick.
If this is what you meant when you wrote 'p.s. I've already tried special
pasting.' I apologise. Let us know if that is the case and we'll rethink!!
 
Hi
Or even saving one step:
- copy an eMPTY cell
- Select the cells to paste it to
- Edit > Paste Special > Add > OK
 
Try selecting a column, then:
<Data> <TextToColimns> <Next> <Finish>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

We brought over tons of information from PeopleSoft into
an excel spreadsheet for reporting purposes. However,
several columns with numbers came across as '5425 instead
of 5425. End result is I cannot format these columns to
resemble $5,425. As we are talking about 6 columns with
over 2500 I can't really go in by hand to remove these.

Any suggestions would be greatly appreciated...

Thank you!

Michele
p.s. I've already tried special pasting.
 
You may have do to a find and replace of the ' with <blank> then do the paste
special - copy empty cell select column, paste special, add

: We brought over tons of information from PeopleSoft into
: an excel spreadsheet for reporting purposes. However,
: several columns with numbers came across as '5425 instead
: of 5425. End result is I cannot format these columns to
: resemble $5,425. As we are talking about 6 columns with
: over 2500 I can't really go in by hand to remove these.
:
: Any suggestions would be greatly appreciated...
:
: Thank you!
:
: Michele
: p.s. I've already tried special pasting.
 
Back
Top