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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
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:
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Fails to execute with debug pointing to the following line:

iVal = Left(c, Len(c) - 1)
 

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
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
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550

ADVERTISEMENT

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?
 

Andywiz

Board Regular
Joined
May 25, 2005
Messages
238
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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})
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,452
Office Version
  1. 365
Platform
  1. Windows
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})
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

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
Top