Help with separating data!

Question

Because the text to columns choice would only give limited choices as delimiters.  And I wanted to separate data as below.

2-Qatar358.27

4+1Bulgaria172.49

Like I want to separate the text and numbers appearing as text, and also to separate the “-“ and “+”.

Thanks in advance.

Answer 1

Link to a workbook on some file-sharing site (or onedrive or similar) with (plenty) more samples to separate and an example of some expected results (say for the more unusual ones).

Answer 2

 So preferably I would not wish to use VBA.

Okay, no problem, maybe someone else has an idea to accomplish this with a formula.

Andreas.

Answer 3

I just follow the commands supplied to me, but honestly don’t understand a thing with respect to VBA.

I would only rely on the macro commands supplied by the program.

So preferably I would not wish to use VBA.

Answer 4

You are an experienced user, you know VBA, you know what a UDF is.

Declare a variable InNumber as Boolean, if the first char of the text is a number or a dot InNumber is True. Declare a variable FromPos as Long, initialize with 1.

For i=1 to Len(Text) , check if the char at the current position i is a number or dot, compare this with InNumber. If the result is the same, continue. Otherwise, copy the part from FromPos till the current position-1, set FromPos = current position and toggle InNumber.

After the loop, i = Len(Text)+1, copy the part from FromPos till i-1 to grab the last part.

IMHO that should work, what do you think?

Andreas.