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.

 

[Kamolga]

for example:
1 X 2 X 3

A1- 1
A2- 2
A3- 3​

That is how I would do it:
Put the values to split in A1, A2,A3, etc.
Select the cells I want to split, then
1.in data tab, click on Text to columns
2.Select delimited and click next
3.In delimiter select space
4.Also select 'other' and put X
5. Click next and finish
6. Delete column B and D (columns with 'X')

My numbers are now split in columns A,B and C. Copy-paste "transpose" to have them split in rows 1,2,3

 

[BENNASR]

hi from TUNISIA
you can do it with VBA

Sub test()    Dim Tableau
    Dim i As Integer
    Dim j As Integer
    Dim L As Long
    With Sheets("Feuil1")
    L = .Range("A" & Rows.Count).End(xlUp).Row
    Z = 2
    For j = 2 To L
    Tableau = Split(.Cells(j, 1), " X ")
    For i = 0 To UBound(Tableau)
        .Cells(j, i + 2) = Chr(j + 63) & (i + 1) & "-" & Tableau(i)
    Next i
    Next j
    End With
End Sub

 

[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?

Assuming your values are in cells A1, B1, C1, etc. and that you want the individual numbers in the cells below them (hence, you meant to write A2, B2 and not A1, B1 for your example outputs), put this formula in cell A2 and copy it across to the last data cell on Row 1, then copy all of those formula down to the next row (or down two rows if you want all three numbers separated out)...

=0+TRIM(MID(SUBSTITUTE(" X "&A$1," X ",REPT(" ",99)),ROWS($2:2)*99,99))