Try
=MID(A1,15,7)
GEMMA 30 X 44 = MURANO BEIGE | ||
GEMMA 30 X 44 = MURANO BROWN | ||
GEMMA 42 X 42 = MURANO BEIGE | ||
GEMMA 42 X 42 = MURANO BROWN but does not work with following
|
NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT | 60 X 60 |
NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY | 60 X 60 |
NIRO LUCIDO 60 X 60 = GMA 33 ABREE |
=wd(A1)
Function wd(s As String) As String ' handy for extracting item dimensions
With CreateObject("VBScript.RegExp")
.Pattern = "(\b[0-9]{2,}\s[X]\s[0-9]{2,}\b)"
If .Test(s) Then wd = .Execute(s)(0).SubMatches(0)
End With
End Function
Excel 2003 | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | GEMMA 30 X 44 = MURANO BEIGE | 30 X 44 | |||
2 | GEMMA 30 X 44 = MURANO BROWN | 30 X 44 | |||
3 | GEMMA 42 X 42 = MURANO BEIGE | 42 X 42 | |||
4 | GEMMA 42 X 42 = MURANO BROWN | 42 X 42 | |||
5 | NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT | 60 X 60 | |||
6 | NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY | 60 X 60 | |||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C1 | =wd(A1) | |
C2 | =wd(A2) | |
C3 | =wd(A3) | |
C4 | =wd(A4) | |
C5 | =wd(A5) | |
C6 | =wd(A6) |
Markmzzz , Yes works not sure why does not did not copy for the 3rd cell
NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT 60 X 60 NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY 60 X 60 NIRO LUCIDO 60 X 60 = GMA 33 ABREE
<tbody>
</tbody>
NIRO CEMENTUM 60 X 60 GCM 01 WHITE MATT | 60 X 60 |
NIRO REGAL 60 X 60 = GMR 81 LIGHT GREY | 60 X 60 |
NIRO LUCIDO 60 X 60 = GMA 33 ABREE | 60 X 60 |
GEMMA 30 X 44 = MURANO BEIGE | 30 X 44 |
GEMMA 30 X 44 = MURANO BROWN | 30 X 44 |
GEMMA 42 X 42 = MURANO BEIGE | 42 X 42 |
GEMMA 42 X 42 = MURANO BROWN | 42 X 42 |
****************************************** | ******* |
In A1
=IFERROR(TRIM(MID(REPT(" ",LEN(A1))&SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",REPT(" ",LEN(A1))&SUBSTITUTE(A1," ",REPT(" ",LEN(A1))))-2*LEN(A1),4*LEN(A1))),"")
And copy down.
In A1...
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" X ",A1)-1)," ",REPT(" ",99)),99))&" X "&REPLACE(LEFT(A1,FIND(" ",A1,FIND(" X ",A1)+3)-1),1,FIND(" X ",A1)+2,"")
Copy down as needed
I am not sure I would call that a "small modification", but I like the resulting formula that you got from it. Nice!A small modification in my formula:
Code:=TRIM(MID(SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1))),SEARCH(" X ",SUBSTITUTE(" "&A1," ",REPT(" ",LEN(A1)))&" X ")-2*LEN(A1),4*LEN(A1)))