-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
EXTRACT specific text into next cell
- Thread starter makinmomb
- Start date
Yeah will work for the following
| 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
|
Upvote
0
Markmzzz , Yes works not sure why does not did not copy for the 3rd cell
<colgroup><col width="297" style="width: 223pt; mso-width-source: userset; mso-width-alt: 10861;">
<col width="64" style="width: 48pt;">
<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 |
Upvote
0
Michael M
Well-known Member
- Joined
- Oct 27, 2005
- Messages
- 21,835
- Office Version
- 365
- 2019
- 2013
- 2007
- Platform
- Windows
What about a UDF
Place in a standard module and use
Place in a standard module and use
Code:
=wd(A1)
Code:
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) | |
Upvote
0
No problem here. Look at this:
Layout
| 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 |
| ****************************************** | ******* |
<tbody>
</tbody>
Formula
Code:
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.
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,465
- Office Version
- 365
- Platform
- Windows
This formula seems to work also (change each of the FIND functions to SEARCH functions if the X can be either upper or lower)...
Note: Because this formula does not use IFERROR, it will also work in XL2003 and below (if that should be a consideration).
Code:
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
Last edited:
Upvote
0
Rick Rothstein
MrExcel MVP
- Joined
- Apr 18, 2011
- Messages
- 38,465
- Office Version
- 365
- Platform
- Windows
I am not sure I would call that a "small modification", but I like the resulting formula that you got from it. Nice!
Upvote
0
