how do I use wildcards in an excel formula to help replace?

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I work with bandwidth which is normally in raw number format. This week someone sent me a massive spreadsheet with information in text format similar to the following examples:

204.36k (item in kilobits)
202.56M (item in megabytes)
1.257G (item in gigabytes)

My aim is to standardize cell contents as a number in megabytes. I would like to be able to do is write code that will look in a range, lets say AU2:DV1500, and whenever it finds in a cell(items aren't case sensitive):

k - remove the k and multiply cell contents by .001
m - remove the m
G - remove the G and multiply cell contents by 1000

Appreciate any assistance that can be provided on this.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You could try something like this:

Code:
Sub StandardiseBandwidth()

Dim c As Range, iVal As Long
    For Each c In Range("AU2:DV1500").Cells
        If Not (IsEmpty(c)) Then
            iVal = Left(c, Len(c) - 1)
            Select Case Right(c, 1)
                Case "k"
                    c = iVal * 0.001
                Case "m"
                    c = iVal
                Case "g"
                    c = iVal * 1000
            End Select
        End If
    Next c

End Sub

Come back if it doesn't do the trick or you want help implementing it.

Andywiz :eek:
 
Upvote 0
Fails to execute with debug pointing to the following line:

iVal = Left(c, Len(c) - 1)
 
Upvote 0
What error message does it give?

Do you have any cells in your sheet that end with a letter other than k,m or g?

You could of course always do this in excel with formulae rather than VBA. A simple if would do it - for example for the data in cell A1:

=IF(Right(A1,1)="k",left(A1,Len(A1)-1)*.001,IF(Right(A1,1)="g",left(A1,Len(A1)-1)*1000,IF(Right(A1,1)="m",left(A1,Len(A1)-1),A1)))

Andywiz
 
Upvote 0
I took another crack at it and this time the code ran through and successfully replaced the k's but didn't proceed to do likewise to the m's and g's. What might be the missing element to the code to get it to finish the job?
 
Upvote 0
without seeing your spreadsheet it's hard to say. What are the answers to my previous questions? What is the complete range reference for the cells you are trying to change?

I think with so many unknown variables you'd be better trying the formula I mention above - this way you won't be altering the original data set and it will be easier to correct for the unknown variables.

A
 
Upvote 0
Also, with lesser number of function calls...

=SUBSTITUTE(UPPER(A1),LOOKUP(9.99999999999999E+307,SEARCH({"K","M","G"},A1),{"K","M","G"}),"")*LOOKUP(9.99999999999999E+307,SEARCH({"K","M","G"},A1),{0.001,1,1000})
 
Upvote 0
If the data consistently concludes with a single k, m or g then perhaps this:
=LEFT(A1,LEN(A1)-1)*LOOKUP(RIGHT(A1),{"G","K","M"},{1000,0.001,1})
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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