Extracting PARTIAL words from cell

Rk_Digital

New Member
Joined
Nov 15, 2019
Messages
3
I'm in a worksheet and trying to extract the words after the 3rd and 4th hyphens when they're available in column A (see table below for representation).


Digital - workbook - sheet
Digital - workbook - app
Digital - workbook - test
Digital - workbook - sheet - max - version1
Digital - workbook - sheet - max - version2
Digital - workbook - sheet - max - version1
Digital - workbook - sheet - max - version2

<tbody>
</tbody>

The first 3 rows only have 3 words with 2 hyphens - for these rows, I wouldn't want anything to extract into column B. I would want those cells to stay blank, but when the 4th row comes up, there are 5 words with 4 hyphens and I need the words (with the hyphen) that are listed after the 3rd hyphen to show up in column B.

I'm looking for a formula or VBA function/sub that I can continuously run down column B that will be able to extract the portion after the 3rd hyphen.

For example, if the formula ran correctly, the first 3 rows in column B would be blank and then next 4 would be as follows...
max - version1
max - version2
max - version1
max - version2

<colgroup><col></colgroup><tbody>
</tbody>

Any help would be GREATLY appreciated!

Thank you very much!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
Welcome to the Board!

Let's pick a character that we would never have in our data, like maybe a tilde (~). Then as long as the values between your hyphens aren't longer than 255 characters, I think something like this should work (for an entry in cell A1):
Code:
=SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1,"-","~" & REPT(" ",255)),750,1000)),"~","-")
 
Last edited:

Rk_Digital

New Member
Joined
Nov 15, 2019
Messages
3
Thanks Joe4! Unfortunately, this didn't work. I gave it a try, but it just makes all of the cells blank.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
It worked for me.

Did you remember to change the cell references to match the cell your data resides in?

Did you try it on an example like you posted in your original post, or did you try it on other data?
If on other data, can you post an example of the data you actually did try it on, so I can try it on that to?
Otherwise, you and me are comparing "apples and oranges".
 
Last edited:

Rk_Digital

New Member
Joined
Nov 15, 2019
Messages
3
I did forget to change something, but it's working great now! Thank you so much!
I really appreciate your help and how quick you were!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
You are welcome.
 

Forum statistics

Threads
1,078,491
Messages
5,340,683
Members
399,389
Latest member
JayNExcel

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top