Formula to show only certain characters

[Kermy812]

is there a way to make a formula to only show certain characters?
I basically only want to show 0 though 9, periods, and a lowercase "x"?
I need my results to look like B below:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD="width: 75"]A[/TD]
[TD="width: 75"]B[/TD]
[/TR]
[TR]
[TD]8.5 in x 11 in[/TD]
[TD]8.5x11[/TD]
[/TR]
[TR]
[TD]8.5 x 11 topper[/TD]
[TD]8.5x11[/TD]
[/TR]
[TR]
[TD]8.5 x 11 Store stand[/TD]
[TD]8.5x11[/TD]
[/TR]
[TR]
[TD]8.5 x 11 POP[/TD]
[TD]8.5x11[/TD]
[/TR]
[TR]
[TD]2" x 10"[/TD]
[TD]2x10[/TD]
[/TR]
[TR]
[TD]8.5X 11 - diecut[/TD]
[TD]8.5x11[/TD]
[/TR]
[TR]
[TD]5 x 7[/TD]
[TD]5x7[/TD]
[/TR]
[TR]
[TD]8.5x11[/TD]
[TD]8.5x11[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Is there any chance that the text after the dimension might contain a number in it? For example, something like this made-up example...

8.5 x 11 Size A4 Paper

 

[Scott Huish]

Here's a UDF that I think will do that:

Function GetMeasurements(r As String) As String
On Error Resume Next
Dim t
r = Replace(LCase(r), " ", "")
r = Application.WorksheetFunction.Trim(Replace(r, "x", " x "))
t = Split(r)
If t(1) <> "x" Then Exit Function
GetMeasurements = Val(t(0)) & "x" & Val(t(2))
End Function

Excel 2010
	A	B
1	8.5 in x 11 in	8.5x11
2	8.5 x 11 topper	8.5x11
3	8.5 x 11 Store stand	8.5x11
4	8.5 x 11 POP	8.5x11
5	2" x 10"	2x10
6	8.5X 11 - diecut	8.5x11
7	5 x 7	5x7
8	8.5x11	8.5x11
9	8.5 x 11 Size A4 Paper	8.5x11
Sheet1

Cell Formulas
Range	Formula
B1	=GetMeasurements(A1)
B2	=GetMeasurements(A2)
B3	=GetMeasurements(A3)
B4	=GetMeasurements(A4)
B5	=GetMeasurements(A5)
B6	=GetMeasurements(A6)
B7	=GetMeasurements(A7)
B8	=GetMeasurements(A8)
B9	=GetMeasurements(A9)

 

[Kermy812]

That works great! Thank you! I was doing this with 60 or so replaces in a macro, and in seems like each month, I have to keep adding a few more. is there a chance you briefly tell me what's each line is doing? Just tying to learn as much as I on the fly as it is.

 

[Scott Huish]

In writing explanation of the code, I realized the TRIM is not necessary, so here is the revised code with an explanation to follow:

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

 

[Kermy812]

Thanks a bunch - This is working perfect for me!