Excel formula help

[mak]

if i have in cell a1 this

A) 2.15 2.45 3.15 3.45 4.15 4.45 5.15

how can i "write" a formula to have each number in different cells?

b1=2.15 -- c1=2.45 etr

thnx

[osknows]

For me the easiest way is to put this in a MODULE

Code: Select all

Function Sp(cell As Range, count As Integer)

If count <= UBound(Split(cell.Value, " ")) Then
Sp = Split(cell.Value, " ")(count)

Else

Sp = "Count greater than array size" ' change error accordingly

End If
End Function


Then in any cell the formula
=Sp(A1,0) would equal 2.15
=Sp(A1,1) equals 2.45

etc


If you wanted something you could copy and paste down without manually entering the count number then in cell B2 try
=sp($A$1,ROW(B2)-2)

the -2 must match the starting row so if you start at say row 6 formula would be =sp($A$1,ROW(B6)-6)

[mak]

Os thanks it works fine.
I spend the entire day mostly trying to reformat (through formulas) the output of each cell

for example
2.15 = 1415 [ time -> without : ]
6.30 = 1830

I finally got it.Please out of curiosity & educative reasons, is it very hard to "write" a function about it?

[osknows]

Hi mak,

There's already an excel function call SUBSTITUTE

Eg

=SUBSTITUTE(A1,".","")

or using the earlier user function

=SUBSTITUTE(Sp(A1,0),".","")

I suppose if you really wanted to you could embeed it in earlier code

Code: Select all

Function Sp(cell As Range, count As Integer)

If count <= UBound(Split(cell.Value, " ")) Then
Sp = replace(Split(cell.Value, " ")(count) ,".","")

Else

Sp = "Count greater than array size" ' change error accordingly

End If
End Function


[mak]

Os
I used the SUBSTITUTE function the difficult thing was to replace 2 with 14
3 with 15 etr...

[osknows]

Sorry Mak, I didn't read it properly. I thought you were just trying to omit the .

This could go in a MODULE

Code: Select all

Function ConvertTime(cell As Range)

ConvertTime = Split(cell.Value, ".")(0) + 12 & Split(cell.Value, ".")(1)

End Function


if A1 = 2.15

then =ConvertTime(A1)
= 1415

[mak]

Os i will try it later

* what a waste of my time
i have written 5 different formulas to achive this

Thanks