number separation

[badmi]

Hi all,
I have a situation, wherein i need to separate the below numbers and will be input to other info.

1.5 X 3 X 8
3 X 1.5 X 10

I need to get first numbers-1.5
3
I need to get Mid numbers- 3
1.5
and note there is space and X for mid numbers.

 

[Rick Rothstein]

Hi all,
I have a situation, wherein i need to separate the below numbers and will be input to other info.

1.5 X 3 X 8
3 X 1.5 X 10

I need to get first numbers-1.5
3
I need to get Mid numbers- 3
1.5
and note there is space and X for mid numbers.

What about the 8 and 10... you do not need to separate them out?

Do you actually mean there is a space and X and another space for mid numbers?

When you say you need to separate them... you mean into adjacent cells, correct? Or is this a VBA question and you need them in variables?

 

[badmi]

No sir, but i am interested to know abt it.

 

[Rick Rothstein]

No sir, but i am interested to know abt it.

You may have missed it, but I edited my response to include two additional questions... refresh your browser page to see them.

 

[badmi]

Point 2-yes there is a space
Point 3-yes i need them in adjacent cells. It is not a VBA question.

 

[Rick Rothstein]

Assuming your values start in cell A1, put this formula in cell B1 and copy it across to Column C (or Column D if you want all three numbers)...

=0+TRIM(MID(SUBSTITUTE(" X "&$A1," X ",REPT(" ",99)),COLUMNS($B:B)*99,99))

 

[badmi]

Thank you Sir.
I almost got it.
But if required output are one below the other in column.

 

[Rick Rothstein]

Thank you Sir.
I almost got it.
But if required output are one below the other in column.

I'm about to go to sleep, so I may not answer your response for awhile unless you respond quickly.

So if you have, say, 1 X 2 X 3 in cell A1, then you want the 1 in cell A2, the 2 in cell A3 (and possibly the 3 in cell A4), is that correct?

If so, where is your second value... cell B1? Or was it in cell A2 but got pushed down to cell A4 (or possibly A5 if three values are retrieved)?

In other words, exactly how is your existing data laid out and where exactly to you want the output to go to?

 

[badmi]

for example:
1 X 2 X 3

A1- 1
A2- 2
A3- 3

1.5 X 3 X 8

B1-1.5
B2-3
B3-8

 

[Rick Rothstein]

Are you saying the "1 X 2 X 3" is in cell A1 and afterwards you want the 1 to remain in cell A1 but the 2 and 3 to be split down under it? If so, that cannot be done with formulas as it would require the value and a formula operating on it to be in the same cell which Excel cannot do. So, if that is your layout and need, then you will need a VBA macro to accomplish what you want. So, did you really mean "1 X 2 X 3" is in cell A1 and so it the 1 afterwards?