add inch sign beside numbers in string

hamada20062010

New Member
Joined
Mar 5, 2016
Messages
3
Hello, Itry " after numbers to make it looks like inch format
Can I convert A1:A16 to B1:B16 ?

A1:A16=
45t*&65/
Size: 3.5x4 (Pre- to 2x3.5),
Size: 4.75x9.5 (way to 4.75x4.75),
Size: 2.5x3.5,
Size: A-1 (3.625 x 5.125) Invite
Size: #9 (3.875 x 8.875) .Com
Size: 7x4.75 (Pre-to 3.5x4.75),
Size: 9x6.25 (Pre- to 4.5x6.25),
Size: 4.25x11 (ED E),
Size: 4.5x6.5,
Size: 3.5x11 BC and FC,
Size: CU 1.0x1.0,
Size: Ov 2x3.5,
Size: 5x11 filled in 5x5.5,
Size: 3.35x2.17,
Size: Ov 3 2/3x4 5/7,

Result needed B1:B16
45t"*&65"/
Size: 3.5"x4" (Pre- to 2"x3.5"),
Size: 4.75"x9.5" (way to 4.75"x4.75"),
Size: 2.5"x3.5",
Size: A-1 (3.625" x 5.125") Invite
Size: #9 (3.875" x 8.875") .Com
Size: 7"x4.75" (Pre-to 3.5"x4.75"),
Size: 9"x6.25" (Pre- to 4.5"x6.25"),
Size: 4.25"x11" (ED E),
Size: 4.5"x6.5",
Size: 3.5"x11" BC and FC,
Size: XU 1.0"x1.0",
Size: xv 2"x3.5",
Size: 5"x11" filled in 5"x5.5",
Size: 3.35"x2.17",
Size: Ov 3 2/3"x4 5/7",

<tbody>
</tbody>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Welcome to the board.

It would require VBA, and i don't think it would be trivial.

Good set of examples.
 
Last edited:
Upvote 0
This is kind of brute-force, and by no means includes all the areas you need, but it is a start for you...
A​
B​
1​
45t*&65/45t''*&65''/
2​
Size: 3.5x4 (Pre- to 2x3.5),Size: 3.5''x4'' (Pre- to 2''x3.5''),
3​
Size: 4.75x9.5 (way to 4.75x4.75),Size: 4.75''x9.5'' (way to 4.75''x4.75''),
4​
Size: 2.5x3.5,Size: 2.5''x3.5,
5​
Size: A-1 (3.625 x 5.125) InviteSize: A-1'' (3.625 ''x 5.125'') Invite
6​
Size: #9 (3.875 x 8.875) .ComSize: #9'' (3.875 ''x 8.875'') .Com
7​
Size: 7x4.75 (Pre-to 3.5x4.75),Size: 7''x4.75'' (Pre-to 3.5''x4.75''),
8​
Size: 9x6.25 (Pre- to 4.5x6.25),Size: 9''x6.25'' (Pre- to 4.5''x6.25''),
9​
Size: 4.25x11 (ED E),Size: 4.25''x11'' (ED E''),
10​
Size: 4.5x6.5,Size: 4.5''x6.5,
11​
Size: 3.5x11 BC and FC,Size: 3.5''x11 BC and FC,
12​
Size: CU 1.0x1.0,Size: CU 1.0''x1.0,
13​
Size: Ov 2x3.5,Size: Ov 2''x3.5,
14​
Size: 5x11 filled in 5x5.5,Size: 5''x11 filled in 5''x5.5,
15​
Size: 3.35x2.17,Size: 3.35''x2.17,
16​
Size: Ov 3 2/3x4 5/7,Size: Ov 3 2''/3''x4 5''/7,
B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,")","'')")," (","'' ("),"x","''x"),"/","''/"),"*","''*")
copied down
Note that I used 2 ' instead of 1 " (looks the same though)
 
Upvote 0
Here is macro solution:
Rich (BB code):
Sub AddInchSymbol()
 
  Dim a
  Dim r As Long
  Dim s As String
  Dim Rng As Range
 
  ' Limit Range("A:A") by the used range
  Set Rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
  If Rng Is Nothing Then Exit Sub
 
  ' Copy source values to the array a()
  With Rng
    a = .Value
    If Not IsArray(a) Then
      ReDim a(1 To 1, 1 To 1)
      a(1, 1) = .Value
    End If
  End With
 
  ' Main
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([(x&\s]\d+([.\-\s]\d+)?(\/\d+)?)"
    For r = 1 To UBound(a, 1)
      If Len(a(r, 1)) Then
        s = " " & a(r, 1)
        a(r, 1) = Mid$(.Replace(s, "$1"""), 2)
      End If
    Next
  End With
 
  ' Put result in B
  Rng.Offset(, 1).Value = a
 
End Sub

Source data is in A-column, result is in B-column

Book1
AB
145t*&65/45"t*&65"/
2Size: 3.5x4 (Pre- to 2x3.5),Size: 3.5"x4" (Pre- to 2"x3.5"),
3Size: 4.75x9.5 (way to 4.75x4.75),Size: 4.75"x9.5" (way to 4.75"x4.75"),
4Size: 2.5x3.5,Size: 2.5"x3.5",
5Size: A-1 (3.625 x 5.125) InviteSize: A-1 (3.625" x 5.125") Invite
6Size: #9 (3.875 x 8.875) .ComSize: #9 (3.875" x 8.875") .Com
7Size: 7x4.75 (Pre-to 3.5x4.75),Size: 7"x4.75" (Pre-to 3.5"x4.75"),
8Size: 9x6.25 (Pre- to 4.5x6.25),Size: 9"x6.25" (Pre- to 4.5"x6.25"),
9Size: 4.25x11 (ED E),Size: 4.25"x11" (ED E),
10Size: 4.5x6.5,Size: 4.5"x6.5",
11Size: 3.5x11 BC and FC,Size: 3.5"x11" BC and FC,
12Size: CU 1.0x1.0,Size: CU 1.0"x1.0",
13Size: Ov 2x3.5,Size: Ov 2"x3.5",
14Size: 5x11 filled in 5x5.5,Size: 5"x11" filled in 5"x5.5",
15Size: 3.35x2.17,Size: 3.35"x2.17",
16Size: Ov 3 2/3x4 5/7,Size: Ov 3 2/3"x4 5/7",
Sheet1
 
Last edited:
Upvote 0
This is kind of brute-force, and by no means includes all the areas you need, but it is a start for you...


B1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,")","'')")," (","'' ("),"x","''x"),"/","''/"),"*","''*")
copied down
Note that I used 2 ' instead of 1 " (looks the same though)


Thank you for your help, It didn't work with all elements but I will keep trying:)
 
Upvote 0
@ZVI
Your solution is great, works well.

so I searched and found that link,
https://msdn.microsoft.com/en-us/library/ms974570.aspx
Is this the best topic describes your answer?
Thank you.
Glad it works as expected for you!
The link you provided is good though it’s not full description of regular expressions.
In this link in the article "Search and replace" see also example and description of so called "back reference" used in my code.

I agree with Shg that pattern for your task could be too complicated.
But an extra space char on the left side of the text simplifies the pattern a lot.

Below is a short explanation of the used pattern.

This pattern "([(x&\s]\d+([.\-\s]\d+)?(\/\d+)?)" in words is the (match_pattern_for_inches_or_mumbers) .
In the replace method of regular expressions it is marked as "$1" back reference.

"[(x&\s]" – one of the symbols listed inside the [], that is: "(" or "x" or "&" or any type of the space chars which is coded by "\s".

"\d+" - one or more digits.

"([.\-\s]\d+)?" – symbol "." or "-" or space char with one or more followed digits. The question symbol "?" after group () means that that group/part can or can’t be present in the text.

"(\/\d+)?" – symbol "/" with one or more followed digits. See above what symbol "?" means.
 
Last edited:
Upvote 0
For those who might be interested, the following non-RegExp macro will produce the same output that Vlad's macro does for the given sample data. I did have some trouble deciding on the best way to treat the non-numbers after the first word and settled on a method that checks the character after the preceding space (for the number I suspect to be a number requiring an inch-mark) to see if it is a letter or a # symbol. I can imagine other code possibilities that could require special treatment as well, but without knowing what they are, it is kind of hard to craft a work-around for them. With that said, here is the code I came up with...
Code:
[table="width: 500"]
[tr]
	[td]Sub AddInchSymbol()
  Dim X As Long, Z As Long, Data As Variant, S As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For X = 1 To UBound(Data)
    S = " " & Data(X, 1) & " "
    For Z = Len(S) - 1 To 2 Step -1
      If Not Mid(S, Z, 3) Like "#[ /]#" And Mid(S, Z, 3) Like "#[!0-9.]*" Then
        If Not Mid(S, InStrRev(S, " ", Z) + 1, 1) Like "[#A-Za-z]" Then
          S = Application.Replace(S, Z + 1, 0, """")
        End If
      End If
    Next
    Data(X, 1) = Trim(S)
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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