Need a formula\function that return first and last value within range of numbers

[Davduv10]

Hello everybody,
Hope you can help me with this one:

I have a cell (lets say A1) with different values (inside the cell) consist with a letters and numbers and they all separated with comma and space like this:
C1, C2, C3, XB5, C4, XB3, C5, XB4, L8, C10

I need a formula\function that return first and last value within a range of values inside the cell and write them in 2 separated (lets say B and C) columns like this:
C1 C5
C10 C10
L8 L8
XB3 XB5

*L8 and C10 don't have a follow or lead values so the rage is from L8 to L8 and C10 to C10.

Thanks in advance.

[Glenn Kennedy]

Please attach a sample worksheet. It's really impossible to see what is meant to be in which cell. Refer to the yellow banner (top) for instructions on attaching an Excel file.

[Davduv10]

Hi,
Thank you for your reply.
I uploaded an xl sample.

[Glenn Kennedy]

!!! I don't understand the logic here, at all. Can you explain how you arrive at your expected answers?

[Davduv10]

I'll try to explain.
Maybe "range" is not the right word.

I have values in one cell: "A1, A2, A3, A4, A5"

I wish to get (return) the "from" and the "to" values.
"From" value will be: A1.
"To" value will be: A5.
There are 5 values (Qty) from A1 to A5.

Now if I have a single value like L8 with no follows values before and after it in the cell then the
"From" and the "To" values will be both L8 (From L8 to L8)

[Glenn Kennedy]

This might require VBA.

Clearly the text can be of variable length (A and XB were given as examples). Correct?

Are the numerics at the end always 1 digit?

Can there be numerics in the text portion, eg. XyZ23GGG1, XyZ23GGG2, XyZ23GGG3

Do the numerics always go 1,2,3, etc,?

Please answer carefully AND supply fully representative samples.

Splitting them up is easy enough, but more information is neeeded about the strings themselves.

[Bo_Ry]

Please try Power Query

select A3 > Ribbon Data > from Table> advanced editor > paste below code

Please Login or Register  to view this content.

[Davduv10]

This might require VBA.

Clearly the text can be of variable length (A and XB were given as examples). Correct?

Are the numerics at the end always 1 digit?

Can there be numerics in the text portion, eg. XyZ23GGG1, XyZ23GGG2, XyZ23GGG3

Do the numerics always go 1,2,3, etc,?

Please answer carefully AND supply fully representative samples.

Splitting them up is easy enough, but more information is neeeded about the strings themselves.

The text can be of variable length up to 3 letters.
The numerics at the end can be up to 4 digits.

Can there be numerics in the text portion? NO.
Only up to 3 letters at start and up to 4 digits at the end.

Do the numerics always go 1,2,3, etc,? NO.
The numerics can start from any number except for 0.

Please let me know if more info is required.
Is Bo_Ry's way should do the job?