Split Text

[gheyman]

I need to split Text that would be very similar to below. I need to parse out everything After "Ver. " (including the space) so that it ends up in two fields
What comes after "Ver. " will vary and will not always be 11 characters.

Form This: Q019017-03 FinalSweep_CounterOffer71122, Ver. 2 Amort Mtrl

To

Q019017-03 FinalSweep_CounterOffer71122

2 Amort Mtrl

Thank You.

 

[etaf]

can this be in 2 separate columns from the main data

you are not showing the , in the first string - is that expected

wil the , always be followed with a space and then Ver. - then just change the delimiter to ", Ver."

this may not work for you
how about
textsplit(cell with the full string in, "ver.")

if you need to change the contents of the original cell and add a new column only - then its VBA and NOT my area

Book5
	A	B	C	D
1	Q019017-03 FinalSweep_CounterOffer71122, Ver. 2 Amort Mtrl		Q019017-03 FinalSweep_CounterOffer71122,	2 Amort Mtrl
2	Q019017-03 FinalSweep_CounterOffer71122, Ver. 123452 Amort Mtrl		Q019017-03 FinalSweep_CounterOffer71122,	123452 Amort Mtrl
Sheet1

Cell Formulas
Range	Formula
C1:D2	C1	=TEXTSPLIT(A1,"Ver.")
Dynamic array formulas.

 

[Automatrix]

Power Query solution:

 

[gheyman]

Thank you both. I went with the Power Query solution

 

[etaf]

glad to have helped
I seem to have power query now on my 365 mac osx version - so will have to lean that - i didnt think i had it, as OSX excel .....

 

[Automatrix]

glad to have helped
I seem to have power query now on my 365 mac osx version - so will have to lean that - i didnt think i had it, as OSX excel .....

Not to sound dramatic, but Power Query is a game changer, and does not deserve to be so "secret".

 

[etaf]

Not to sound dramatic, but Power Query is a game changer, and does not deserve to be so "secret".

yep, seen a load of replies here using power query - but i think its only just been released on OSX - wasnt there earlier in year - or i missed it last time i researched

 

[Tom.Jones]

I need to split Text that would be very similar to below. I need to parse out everything After "Ver. " (including the space) so that it ends up in two fields
What comes after "Ver. " will vary and will not always be 11 characters.

Form This: Q019017-03 FinalSweep_CounterOffer71122, Ver. 2 Amort Mtrl

To

Q019017-03 FinalSweep_CounterOffer71122

2 Amort Mtrl

Thank You.

If you have the new function in excel 365, then use:

=TEXTBEFORE(A1,",")