converting number to dollar

[mytyab]

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

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


Please tell me what I'm missing

 

[GS]

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 Special>Unicode 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 Special>Unicode 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

 

[mytyab]

;1613890']> 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 Special>Unicode 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 Special>Unicode 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 ar
arrest reports.

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

 

[Claus Busch]

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.

 

[GS]

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

 

[mytyab]

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

;1613895']Ah.., so the dollar amount are the result of parsing th
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.discussio

 

[Claus Busch]

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.

 

[GS]

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

 

[mytyab]

That is a very possible conclusion.

I get automated emails with arrest reports. I click on the .htm file t
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 remove
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 i
doesn't which is why I came to you guru's. I've done all of you
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.

;1613898']Claus' suggestion worked for me! I suspect you're not givin
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.discussio

 

[GS]

" 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

 

[GS]

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