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

#### jmckeone

##### Well-known Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

Fails to execute with debug pointing to the following line:

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

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

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?

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

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})

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})

Replies
5
Views
553
Replies
6
Views
843
Replies
28
Views
2K
Replies
13
Views
12K
Replies
13
Views
2K

1,219,892
Messages
6,150,814
Members
450,985
Latest member
Andynair7

### 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.

### Which adblocker are you using?

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

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