In writing explanation of the code, I realized the TRIM is not necessary, so here is the revised code with an explanation to follow:
Code:
Function GetMeasurements(r As String) As String
On Error Resume Next
Dim t
r = Replace(LCase(r), " ", "")
r = Replace(r, "x", " x ")
t = Split(r)
If t(1) <> "x" Then Exit Function
GetMeasurements = Val(t(0)) & "x" & Val(t(2))
End Function
Function GetMeasurements(r As String) As String
On Error Resume Next --
Resumes next line of code if an error occurs
Dim t --
Dims a variant variable named t
r = Replace(LCase(r), " ", "") --
Removes all spaces from string
r = Replace(r, "x", " x ") --
Replaces x with space x space, this will be used to split the string in the next line
t = Split(r) --
Creates an array of the value split on the space character
If t(1) <> "x" Then Exit Function --
This checks to see if the 2nd value in the array is an x
GetMeasurements = Val(t(0)) & "x" & Val(t(2)) --
The Val function returns the numeric value of the string excluding all extraneous text at the end, therefore something like 8.5in would become 8.5 puts an x in the middle and does the same thing with the rest of the text.
So with this string: 8.5 in x 11 in
It would become 8.5inx11in
The x would be replaced with space x space so then
8.5in x 11in
It then splits this into an array on the space, so t(0) would be 8.5in, t(1) would be x, t(2) would be 11in
t(1) does equal x so, it returns the Val of t(0) which is 8.5 followed by an x then the val of t(2) which is 11
so it returns:
8.5x11