I know this should be easy but it isn't.

I copy html spreadsheets off a web link in an email that has columns of data. One of the fields has dollar amounts like 1,565.00 that are formatted as general. I need to convert this to a dollar amount like $1,565. When I try to format the field to currency, nothing happens. I tried copying and pasting values in all the different formats. I made sure the column isn't locked. I have 3,500 rows of different amounts that I don't want to manually change.


Please tell me what I'm missing.

I know this should be easy but it isn't.

I copy html spreadsheets off a web link in an email that has columns
of data. One of the fields has dollar amounts like 1,565.00 that are
formatted as general. I need to convert this to a dollar amount like
$1,565. When I try to format the field to currency, nothing happens.
I tried copying and pasting values in all the different formats. I
made sure the column isn't locked. I have 3,500 rows of different
amounts that I don't want to manually change.


Please tell me what I'm missing.

Assuming you did Copy/Paste instead of Copy/Paste SpecialUnicode Text,
the column with the dollar amounts was (by default) converted to text
during Paste. Select a cell in the dollar amount col and look in the
formula bar for a leading apostrophe...

'1,565.00

...whereby you need to use Copy/Paste SpecialUnicode to get Excel to
use your format. I use this method when pulling data in from Word
tables and it imports correctly.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thank you for the suggestion. But that didn't work.

The field looks like this "Total Bail Amount: 20,000.00". Yes these are arrest reports.

I run a custom macro to format the 27 columns of information into something workable. In the macro I remove the alpha characters from this column. With or without the alpha characters, I don't see any leading apostrophe.

Hi,

Am Mon, 16 Sep 2013 06:30:00 +0100 schrieb mytyab:

I copy html spreadsheets off a web link in an email that has columns of
data. One of the fields has dollar amounts like 1,565.00 that are
formatted as general. I need to convert this to a dollar amount like
$1,565. When I try to format the field to currency, nothing happens. I
tried copying and pasting values in all the different formats. I made
sure the column isn't locked. I have 3,500 rows of different amounts
that I don't want to manually change.

click on the column header to select the column and format the column
with your wished format and choose Data = TextToColumns = Fixed width
= Finish


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Ah.., so the dollar amount are the result of parsing the original field
value. In this case Claus' suggestion is the way to go because it will
work with resulting data from Copy/Paste as well.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Very good suggestion but unfortunately it didn't work either.

Hi,

Am Mon, 16 Sep 2013 20:12:13 +0100 schrieb mytyab:

Very good suggestion but unfortunately it didn't work either.

can you upload your file and post us the link?


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus' suggestion worked for me! I suspect you're not giving us
accurate details about the subject data!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

That is a very possible conclusion.

I get automated emails with arrest reports. I click on the .htm file to open the web page.

I copy all the data and past into Excel.

The cell comes in as locked and formatted as general.
"Total Bail Amount: 2,508.00*"

I run a macro to resort the data into a workable format, which removes the alpha characters from this field." 2,508.00*" .

Now I just need to format this to currency "$2,508".

It really should be as simple as formatting the cell to currency but it doesn't which is why I came to you guru's. I've done all of your suggestions to the raw data and to the data after I run the macro.

I am sure someone out there knows how to make this work.

" 2,508.00*"

This is why it doesn't work! Your parsing process needs to strip out
the leading space and trailing asterisk.

Here's a macro that parses the dollar amount from the original
string...

Sub ParseCurrency()
Dim vData, vTmp, n&, lLastCell&
lLastCell = Cells(Rows.Count, Selection.Column).End(xlUp).Row

vData = Selection.Resize(lLastCell, 1)
For n = LBound(vData) To UBound(vData)
vTmp = Split(vData(n, 1), ": ")
vData(n, 1) = Replace(vTmp(1), "*", "")
Next 'n
With Selection.Resize(lLastCell, 1): .Value = vData: .Style =
"Currency": End With
End Sub

...where the original string is like "Total Bail Amount: 2,508.00*" for
each entry. *You must select the first cell of data containing dollar
amounts before running the macro*

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

A revised version to handle first row not being row 1...

Sub ParseCurrency()
Dim vData, vTmp, n&, lStart&, lEnd&, lCol&
With Selection: lStart = .Row: lCol = .Column: End With
lEnd = Cells(Rows.Count, Selection.Column).End(xlUp).Row

vData = Range(Cells(lStart, lCol), Cells(lEnd, lCol))
For n = LBound(vData) To UBound(vData)
vTmp = Split(vData(n, 1), ": ")
vData(n, 1) = Replace(vTmp(1), "*", "")
Next 'n
With Range(Cells(lStart, lCol), Cells(lEnd, lCol))
.Value = vData: .Style = "Currency"
End With
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion