View Single Post
 
Old 09-26-2022, 12:05 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 873
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Assumed the rule you need to use is always to extract a string having at least from one side enclosed with " - ", attached is an example to extract up to 4 such parts of original string. I used the character "|" to replace "-" when searching for specific occurrence of "-" in text. In case "|" is used in your data, you have to use some other unused replacement there.

You can simplify some of those formulas - but I did make them as uniform as possible. So when you want e.g. to expand formulas for 5 parts returned, simple copy-pasting of some existing formula, and some edit of it, will do.

In case the splitting your data is once-only task, you can instead of formulas use Text To Columns feature of excel. As start you have to replace all " - " with some single-character unused string (e.g. with "|"). Then you select the column with your strings (NB! You must have enough empty columns right of it for splited data), select from data menu Text To Columns, select Delimited option, set your split identifier (e.g. "|") as delimiter, and then click on Finish. Instead of single column of data you have now several different ones!
Attached Files
File Type: xlsx ExtractStringParts.xlsx (9.6 KB, 4 views)
Reply With Quote