Find and extract word

R

Rellie

New Member
Joined
Dec 4, 2015
Messages
4
Hi,

I have a spreadsheet that I update once or twice a week where I need to extract just the colour from a string of words and then copy that colour across into the cell to the left of it.

For example one of the cells has "SD | Quick-Fit Sectional Door - Mediterranean Monument,Wood Grain,2400H,4860W" and I need to extract the word "Monument" from the cell and just copy it.

Is anyone able to tell me how I might be able to do it easier than manually having to look through each row?

Thanks :)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Tell us what column we may find the word "Monument" in and then we will copy it over to the column to it's left. Is that the only word we will be looking for?
 
Upvote 0
Give more line of examples and together with the expected results.

Regards
 
Upvote 0
Try this:
Assuming your willing to use Vba scripting. And assuming we are looking for the word "Monument" in column "B"

If it's not column "B" then change "B" marked in red to your wishes.
Use this script:

Code: 
Sub Monument()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "[COLOR="#FF0000"]B[/COLOR]").End(xlUp).Row
    For i = 1 To Lastrow
        If InStr(Cells(i, "[COLOR="#FF0000"]B[/COLOR]").Value, "Monument") Then
            Cells(i, "[COLOR="#FF0000"]B[/COLOR]").Offset(, -1).Value = "Monument"
        End If
    Next
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
I could be looking for a variety of colours for example below are 4 more lines and the colours are "Jasper", "Dune", "Paperbark" and "Surfmist"
Depending on what the product is depends on where the colour is located. Basically i'm trying work out easier ways to extract information based on this manufacturing at risk report as I need to be able to filter by colour if needed.

SD | Quick-Fit Sectional Door - Mediterranean Jasper,Wood Grain,2135H,4880W
SD | Quick-Fit Sectional Door - Regency Dune,Wood Grain,2135H,4880W
SD PANEL | Quick-Fit SD Top Panel (546) Cosmopolitan,Paperbark,Wood Grain,546H,4880W,Top
RD | Roller Door - Ex Sheet (Brisbane Only) - RDA Surfmist,2200H,1640W

<tbody>
</tbody>

<colgroup><col width="1037" style="width:778pt"> </colgroup><tbody>
</tbody>

The end result would like the below with the colours under column K and the description of the product under column L.

JasperSD | Quick-Fit Sectional Door - Mediterranean Jasper,Wood Grain,2135H,4880W
DuneSD | Quick-Fit Sectional Door - Regency Dune,Wood Grain,2135H,4880W
PaperbarkSD PANEL | Quick-Fit SD Top Panel (546) Cosmopolitan,Paperbark,Wood Grain,546H,4880W,Top
SurfmistRD | Roller Door - Ex Sheet (Brisbane Only) - RDA Surfmist,2200H,1640W

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
That worked beautifully. Thank you so much.
Is it possible to search for in the above case Monument but have the word that is put in the other column as something else? For example the script would find "Monument" in column L but place the code of "MN" in column K?
 
Upvote 0
Try using this script:
Code: 
Sub Monument()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim ans As String
ans = InputBox("Enter The Color")
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
    For i = 1 To Lastrow
        If InStr(Cells(i, "L").Value, ans) Then
            Cells(i, "L").Offset(, -1).Value = ans
        End If
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Yes the other word could be something else but if we will be using numerous different words we would need some standard way of knowing what.
For example if "Monument" then "Mo" and if "Apple" then "Ap"
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
238
chickyguy
C
B
  • Question
Excel Membership Data to Word Document
Replies
7
Views
237
Beaglebud
B
S
  • Question
VBA Challenge
Replies
2
Views
239
SnowyBry
S
C
  • Solved
Extract Data from Row
Replies
5
Views
291
Fluff
Fluff
F
  • Question
Extracting specific data to populate specific blocks in excel sheet.
Replies
0
Views
484
flintwc64
F

Forum statistics

Threads
1,214,975
Messages
6,122,538
Members
449,088
Latest member
RandomExceller01

Share this page

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