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>
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Welcome to the board.

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

Good set of examples.
 
Last edited:

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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)
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,802
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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

<table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">45t*&65/</td><td style=";">45"t*&65"/</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Size: 3.5x4 (Pre- to 2x3.5),</td><td style=";">Size: 3.5"x4" (Pre- to 2"x3.5"),</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Size: 4.75x9.5 (way to 4.75x4.75), </td><td style=";">Size: 4.75"x9.5" (way to 4.75"x4.75"), </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Size: 2.5x3.5,</td><td style=";">Size: 2.5"x3.5",</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Size: A-1 (3.625 x 5.125) Invite</td><td style=";">Size: A-1 (3.625" x 5.125") Invite</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Size: #9 (3.875 x 8.875) .Com</td><td style=";">Size: #9 (3.875" x 8.875") .Com</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Size: 7x4.75 (Pre-to 3.5x4.75),</td><td style=";">Size: 7"x4.75" (Pre-to 3.5"x4.75"),</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Size: 9x6.25 (Pre- to 4.5x6.25), </td><td style=";">Size: 9"x6.25" (Pre- to 4.5"x6.25"), </td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Size: 4.25x11 (ED E),</td><td style=";">Size: 4.25"x11" (ED E),</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Size: 4.5x6.5,</td><td style=";">Size: 4.5"x6.5",</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Size: 3.5x11 BC and FC, </td><td style=";">Size: 3.5"x11" BC and FC, </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Size: CU 1.0x1.0, </td><td style=";">Size: CU 1.0"x1.0", </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Size: Ov 2x3.5,</td><td style=";">Size: Ov 2"x3.5",</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Size: 5x11 filled in 5x5.5, </td><td style=";">Size: 5"x11" filled in 5"x5.5", </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Size: 3.35x2.17,</td><td style=";">Size: 3.35"x2.17",</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Size: Ov 3 2/3x4 5/7,</td><td style=";">Size: Ov 3 2/3"x4 5/7",</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120"></p><br /><br />
 
Last edited:

hamada20062010

New Member
Joined
Mar 5, 2016
Messages
3

ADVERTISEMENT

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:)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Nice job, ZVI!
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,802
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
@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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,553
Messages
5,596,806
Members
414,103
Latest member
imamalidadashzada

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
Top