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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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
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,210
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
55,680
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,171,060
Messages
5,873,543
Members
432,982
Latest member
meepio

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