Excel replace characters in string before and after 'x'

Question

Hello I have a column with strings (names of products) in it. Now these are formatted as Name LenghtxWidth, example Green box 20x30. Now I need to change the 20 with the 30 in this example so I get Green box 30x20, any ideas how I can achieve this? Thanks

Answer 1

Here is both a formula solution, as well as a VBA solution using Regular Expressions:

Formula

=LEFT(A1,FIND(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),A1)-1)&
MID(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),SEARCH("x",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))+1,99)&
"x"&
LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),SEARCH("x",TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)

UDF

Option Explicit
Function RevWL(S As String)
    Dim RE As Object
    Const sPat As String = "(\d+.?\d*)x(\d+.?\d*)"

'If L or W might start with a decimal point, and not a digit,
'Then change sPat to:  (\d*.?\d+)x(\d*.?\d+)

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = sPat
    RevWL = .Replace(S, "$2x$1")
End With

End Function

Here is an example of the kinds of data I tested with:

The Formula works by looking at the last space-separated substring which would be LxW, then reversing the portion after and before the x, then concatenating everything back together.

The regex pattern captures the two numbers (could be integers or decimals, so long as the start with an integer -- although that could be changed if needed), and reversing them.

Here is a more detailed explanation of the regex (and the replacement string) with links to a tutorial:

(\d+.?\d*)x(\d+.?\d*)

(\d+.?\d*)x(\d+.?\d*)

Options: Case insensitive; ^$ don’t match at line breaks

• Match the regex below and capture its match into backreference number 1 (\d+.?\d*)
  • Match a single character that is a “digit” \d+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Match any single character that is NOT a line break character .?
    • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
  • Match a single character that is a “digit” \d*
    • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *
• Match the character “x” literally x
• Match the regex below and capture its match into backreference number 2 (\d+.?\d*)
  • Match a single character that is a “digit” \d+
    • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +
  • Match any single character that is NOT a line break character .?
    • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
  • Match a single character that is a “digit” \d*
    • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *

$2x$1

• Insert the text that was last matched by capturing group number 2 $2
• Insert the character “x” literally x
• Insert the text that was last matched by capturing group number 1 $1

Created with RegexBuddy

Answer 2

Ok, so it is really easier to use VBA, but if you want only some formulas you can use some columns to split your text and then concatenate your cells.

Here is a little example:

Of course B1-4 are optional. It is here only to have something more readable, but you can do use only one formula

=CONCATENATE(LEFT(A1, SEARCH(" ",A1,1)-1)," ",RIGHT(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)),LEN(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)))-SEARCH("x",RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)),1)),"x",LEFT(RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)), SEARCH("x",RIGHT(A1,LEN(A1)-SEARCH(" ",A1,1)),1)-1))

---

If you have several spaces in your names, you can use this formula that will search the last space in the text

=CONCATENATE(LEFT(A1, SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)," ",RIGHT(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),LEN(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))-SEARCH("x",RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),1)),"x",LEFT(RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))), SEARCH("x",RIGHT(A1,LEN(A1)-SEARCH("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),1)-1))

Answer 3

Here is a VBA solution that will work for you:

Option Explicit

Function Switch(r As Range) As String

Dim measurement As String
Dim firstPart As String
Dim secondPart As String

measurement = Right(r, Len(r) - InStrRev(r, " "))
secondPart = Right(measurement, Len(measurement) - InStr(1, measurement, "x"))
firstPart = Left(measurement, InStr(1, measurement, "x") - 1)
Switch = Left(r, InStrRev(r, " ") - 1) & " " & secondPart & "x" & firstPart

End Function

You can paste this in a regular module in the VBE (Visual Basic Editor) and use it as a regular function/formula. If your value is in cell A1 then type =Switch(A1) in cell B1. Hope it helps!