Find all instances of strings between 2 different characters

[jllevent]

Hi,

I have text in a single excel cell which looks like this:

* Apple: some general text. * Pear: more text. * Orange:some more text.

I need to extract the each instance of the strings between the characters * and :, and put them in separate, adjacent cells.

e.g.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]Above Text[/TD]
[TD]Apple[/TD]
[TD]Pear[/TD]
[TD]Orange[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this with either excel formulas in each of the adjacent cells, or else via a macro?

Thanks in advance.

 

[FormR]

Hi, here is a formula you can try - it's origins can be found here:

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

Excel 2013/2016
	A	B	C	D
1	* Apple: some general text. * Pear: more text. * Orange:some more text.	Apple	Pear	Orange
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"*",":"),":",REPT(" ",99)),COLUMNS($B1:B1)*2*99-98,99))

 

[MickG]

Code Option:-

[COLOR="Navy"]Sub[/COLOR] MG12Feb22
[COLOR="Navy"]Dim[/COLOR] sp [COLOR="Navy"]As[/COLOR] Variant, Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1")
sp = Split(Rng, "*")
[COLOR="Navy"]For[/COLOR] n = 1 To 3
    Rng.Offset(, n) = Split(sp(n), ":")(0)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jllevent]

These both work great for me. Thank you for the responses!

 

[MickG]

You're welcome