Formula to show only certain characters

Kermy812

New Member
Joined
Feb 22, 2018
Messages
10
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]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Last edited:
Upvote 0
Here's a UDF that I think will do that:

Code:
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
AB
18.5 in x 11 in8.5x11
28.5 x 11 topper8.5x11
38.5 x 11 Store stand8.5x11
48.5 x 11 POP8.5x11
52" x 10"2x10
68.5X 11 - diecut8.5x11
75 x 75x7
88.5x118.5x11
98.5 x 11 Size A4 Paper8.5x11
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,903
Messages
6,181,655
Members
453,059
Latest member
jkevin

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
Back
Top