A lookup table should work, try using a vlookup. You want to take the 2 right characters from the address and use that as the lookup value to return the upper case version, roughly like this (assume address in A1):
new value = left(A1, len(A1)-2) & vlookup(right(A1, 2), Q31:R35, 2, false)
also if there is no complex mapping i.e. you are always just capitalizing the last 2 characters you could do this:
new value = left(A1, len(A1)-2) & upper(right(A1, 2))
A lookup table should work, try using a vlookup. You want to take the 2 right characters from the address and use that as the lookup value to return the upper case version, roughly like this (assume address in A1):
new value = left(A1, len(A1)-2) & vlookup(right(A1, 2), Q31:R35, 2, false)
also if there is no complex mapping i.e. you are always just capitalizing the last 2 characters you could do this:
new value = left(A1, len(A1)-2) & upper(right(A1, 2))
If your only goal is to replace the last two characters with their uppercase counterparts then you could use this formula:
=LEFT(A1,LEN(A1)-2)&UPPER(RIGHT(A1,2))
It trims the text to exclude the right two characters while creating a second text string of the right two where it capitalizes them. Then it concatenates the two strings into one.
The trouble with using SUBSTITUTE is that it will replace all occurrences of the last two characters with their uppercase versions. So "123 Funny Street, New York, ny" would become "123 FunNY Street, New York, NY".
How to use SUBSTITUTE() for a range of values?
vba - Advanced substitute in excel with range - Stack Overflow
vba - In Excel, use "Range.Replace" method to substitute characters, but do not affect formulae - Stack Overflow
Substitute Function in Excel VBA for cell range - Stack Overflow
Let's say that there are multiple values like such:
Address 123 Fake Street, Philadelphia, pa 456 Real Street, Houston, tx 789 Soho Street, New York, ny
Originally, I thought I could create a lookup table to search for "pa", "tx", and "ny" so that they would get upper'd: "PA", "TX", "NY".
Ideally, I want to write a function that would have all of the upper values in a new column. Originally, something like this:
=substitute(O33,Q31:Q35,R31:R35)
with Q31:Q35 being the range of lower states, and R31:R35 being the upper states. However, that didn't work out the way I wanted. Is there a way to do the replacement of lower case states without hard coding? I thought a lookup table would work, but it didn't.
I want the end result to be something like this:
Address New Address 123 Fake Street, Philadelphia, pa 123 Fake Street, Philadelphia, PA 456 Real Street, Houston, tx 456 Real Street, Houston, TX 789 Soho Street, New York, ny 789 Soho Street, New York, NY
Without doing this for every row of New Address:
substitute(A2, "pa", "PA") substitute(A3, "tx", "TX") substitute(A4, "ny", "NY")
Ideally, I would like the answer to use the SUBSTITUTE() function. If it's not possible, please let me know.
Range.Replace will always search in formulas.
There is a HasFormula property in the Range object. But for using this you must iterate over all cells in the given Range. If this Range is quite big then this will take quite long if you are trying Replace in each of the cells. So I would only trying Replace if the cell is not empty, is not numeric and not has a formula.
Sub test()
Dim oRange As Range
For Each oRange In ActiveSheet.Range("A1:Z500")
If Not IsEmpty(oRange) And Not IsNumeric(oRange.Value) And Not oRange.HasFormula Then
oRange.Replace What:="sht", Replacement:=ChrW(1097), MatchCase:=True
oRange.Replace What:="Sht", Replacement:=ChrW(1065), MatchCase:=True
End If
Next
End Sub
Oh and you must set MatchCase if the replace shall be case sensitive.
Second approach:
You also could use the VBA Replace instead of Range.Replace as showed in the other answer. But not with Range.Formula but with Range.Value.
This could be faster.
Sub test2()
Dim oRange As Range
For Each oRange In ActiveSheet.Range("A1:Z500")
If Not IsEmpty(oRange) And Not IsNumeric(oRange.Value) And Not oRange.HasFormula Then
oRange.Value = Replace(oRange.Value, "sht", ChrW(1097), , , vbBinaryCompare)
oRange.Value = Replace(oRange.Value, "Sht", ChrW(1065), , , vbBinaryCompare)
End If
Next
End Sub
You have to look in .Formula properties, default Range result give the .Value (ie result), not value.
I will suggest use a loop like :
For Each cell In ActiveSheet.Range("A1:Z500")
cell.Formula = Replace(cell.Formula, "sht", ChrW(1097))
cell.Formula = Replace(cell.Formula, "Sht", ChrW(1095))
Next
Try this. Note that it uses the VBA Replace function, so you need to rename your 'Replace` subroutine.
Sub ReplaceText()
For Each c In Sheets("Sheet1").Range("A1:A629").Cells
c = Replace(c.Value, ",", ";")
Next c
End Sub
Note: This will only work if you have values in the cells, no Formulas. Because Excel has a formula length limit of 1024 characters. But given you have this specific error, your cells must not be formulas.
Try this
Sub Replace()
Dim rng As Range, cell As Range
Set rng = Sheets("Sheet1").Range("A1:A629")
For Each cell In rng
cell = WorksheetFunction.Substitute(cell, ",", ";")
Next
End Sub
