Importing data

A

AB

New Member
Joined
Nov 11, 2005
Messages
8
I have over 12 thousand prices in a single column. They look like this.

595000
100250
150099
859900


595000 is supposed to be $5.95
100250 is supposed to be $100.25
150099 is supposed to be $1500.99
859900 is supposed to be $85.99

Help, how do I convert this to the correct amounts.


Thankx in advance
A.B. Smith
http://www.onlinepowersports.com
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Hello, welcome to the board. :)

Here's one way, and it assumes that the last non-zero is the last number in the returned dollar amount, that is, you never have 0 cents, e.g., $1.20. Is this a good assumption? It looks like it based on your sample...

I use the following UDF:

Code: 
Public Function foo(ByRef strIn As String) As Currency
Dim b() As Byte, i As Long, tmpStr As String
Let b = strIn
For i = UBound(b) - 1 To LBound(b) Step -2
    If b(i)<> 48 Then
        ReDim Preserve b(0 To i + 1)
        Exit For
    End If
Next
Let tmpStr = b
Let foo = tmpStr / 100
End Function
As such:
Book1
ABCD
15950005.95
2100250100.25
31500991500.99
485990085.99
Sheet1


It's got some chops; I recalculated b1:b12000 in ~1 Millisecond. ;)
 
Upvote 0
Hi Ken,

48 is very significant, well, in this case...

In the VBA help file (hit F1 in the VBE), search on Character Set. Check out 0-127, and hone in on 48.

See what I mean? :)
 
Upvote 0
Ok, so do I paste foo in the visual basic editor? Right now I am still lost as to what the next step is?

A.B.
 
Upvote 0
yes, paste the entire function into a module in the VBE. Then on your worksheet, call the function with =foo(a1) for example.
 
Upvote 0
Right, using it as a worksheet function, as I did in my example, is one possible implementation.

But get it into a normal module and use it as I did for starters. Alt-F11 to get into the VBE, click Insert->Module and paste it there. Then it's go-time. :)
 
Upvote 0
Still need help - after getting home and trying, here is what I have.
3.89 should be 38.90......3.25 should be 38.90, many of the others work depending on where the zero is.

description imported price converted pricecost imported msrp converted msrp price
A-ARM KIT, TRX250R 86 38900 3.89 59950 59.95 KIT
A-ARM KIT, LOWER, LTZ400 389000 3.89 59950 59.95 KIT
A-ARM KIT, UPPER, LTZ400 325000 3.25 49950 49.95 KIT
F.C. SHOCK SPRING 4.3KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 4.5KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 4.6KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 4.7KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 4.8KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 4.9KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 5.0KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 5.1KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 5.2KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 5.3KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 5.4KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 5.5KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING, 5.6KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 5.7KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 5.9KG 682000 6.82 104950 104.95 EA
F.C. SHOCK SPRING 6.1KG 682000 6.82 104950 104.95 EA
MC/ATV STAND EXTENSIONS 397000 3.97 60950 60.95 KIT
ANTI VIBE TWINWALL INSERT 350000 0.35 49950 49.95 PR
ANTI-VIBRATON BAR INSERTS 350000 0.35 49950 49.95 PR
HP-PX MNT KT ARCTIC 1130000 1.13 149950 149.95 PR
VX301 MNT KT ARCTIC AFS 942000 9.42 124950 124.95 PR
VX PERF MNT KT ARCT AFS 942000 9.42 124950 124.95 PR
AQUACORD PLUS BLK 95000 17950 EA
AQUACORD PLUS BLUE 95000 17950 EA
AQUACORD PLUS RED 95000 17950 EA
AQUACORD PLUS YEL 95000 17950 EA
AQUACORD PLUS BLK 95000 17950 EA
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
dynamic formula help - supply multiple variables and process row by row
Replies
0
Views
575
BrettJH
B
O
  • Solved
VBA for Data w/ Repeating Headers
Replies
9
Views
2K
jolivanes
jolivanes
T
  • Question Question
Need to convert multiple Excel cells into a combined htlm bullet point format
Replies
3
Views
3K
John_w
J
T
  • Question Question
UDF to reformat hierarchical numbering for sorting
Replies
5
Views
3K
tjdickinson
T
R
  • Question Question
Newbie question-Condensing repeating rows with different corresponding data into single columns but keeping data seperate?
Replies
1
Views
198
Trevor_S
T

Forum statistics

Threads
1,224,813
Messages
6,181,112
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top