Looking from left and right till integer/non text

[Benjaminmin]

ola,

I'm trying to separate out key words from stock text into new columns .. i.e.

"#2 Codexis vs #15 Propel Fuels"
"#3 Sapphire Energy vs #14 Mendel Biotechnologies"

To two columns showing:
Codexis and then in next column Proper Fuels
Sapphire Energy and next column Mendel Biotechnologies

i.e. look from right and left until a key marker e.g. a number or as above a #?
Can't seem to do it as obviously text length and word numbers is variable

To complicate some of the company names have an integer in them!

any help much appreciated and sorry if this is a bit basic.

 

[Benjaminmin]

Basically I want to use something that could work like LEFT or RIGHT until a wildcard.
e.g. from the right until "#**"

Is this possible?

 

[Weaver]

If you use

=FIND("#",A1,2)

that should tell you at what position the second # is

then to split the string

=LEFT(A1,FIND("#",A1,2)-1)

is the first half

=MID(A1,FIND("#",A1,2)+1,255)

is the second

HTH

 

[Benjaminmin]

Thank you very much!

 

[Weaver]

No problem. Thanks for the feedback.

W