split cell accross help

[wrighty998]

please help with a formula to split my cell

input cell

DSEN 52, DTOR 3, DAMR 79, SSEN 0, STOR 2, SAMR 5, SPROD YES <tbody> </tbody>

<tbody>
</tbody>

<tbody></tbody>

output cells

52

3

79

0

2

5

yes

<tbody>
</tbody>

 

[lrobbo314]

You can use a custom function. I have your string in cell A1. Then you can put =SplitIt($A$1,COLUMN(A1)) in cell B1 and copy it across to the right. Here is the code for the function.

Function SplitIt(r As Range, idx As Integer) As String
Dim SP() As String
Dim S() As String


SP = Split(r.Value, ", ")
S = Split(SP(idx - 1), " ")


SplitIt = S(1)
End Function

 

[wrighty998]

You can use a custom function. I have your string in cell A1. Then you can put =SplitIt($A$1,COLUMN(A1)) in cell B1 and copy it across to the right. Here is the code for the function.

Function SplitIt(r As Range, idx As Integer) As String
Dim SP() As String
Dim S() As String


SP = Split(r.Value, ", ")
S = Split(SP(idx - 1), " ")


SplitIt = S(1)
End Function

wont work pal,

data is in M2 and im putting formula on a diff tab

 

[lrobbo314]

Seems to work with my testing. Try this, changing Sheet2!$M$2, to whatever your sheet name with the data in it is called.

=SplitIt(Sheet2!$M$2,COLUMN(A1))

 

[wrighty998]

Seems to work with my testing. Try this, changing Sheet2!$M$2, to whatever your sheet name with the data in it is called.

=SplitIt(Sheet2!$M$2,COLUMN(A1))

whats the a1 for pal? still says #NAME ?

 

[lrobbo314]

The custom function has 2 arguments. The first on is the range where your data is. The second one is an index. The code is splitting your data into an array. The array splits up your data using the commas and spaces. DSEN 52, DTOR 3, DAMR 79, SSEN 0, STOR 2, SAMR 5, SPROD YES. So the first item in the array is DSEN 52, the second is DTOR 3, and so on. So column(A1) is just a way to get an number to increment 1,2,3,.... to reference the various elements in the array.

 

[wrighty998]

still says #NAME ?

 

[Joe4]

still says #NAME ?

What is the name of the Module where you put this User Defined Function in?
Are you sure you put it in a Module in the same workbook as your data (if you have more than one Excel file open, it is possible you could have put it in the wrong workbook)?

If you have put it in the correct place, then if you start typing =SplitIt in any cell, the formula assistant should pop-up and it should show you all functions that start with that name.
If you do not see that pop-up, you have put your code in the wrong place.

 

[wrighty998]

What is the name of the Module where you put this User Defined Function in?
Are you sure you put it in a Module in the same workbook as your data (if you have more than one Excel file open, it is possible you could have put it in the wrong workbook)?

If you have put it in the correct place, then if you start typing =SplitIt in any cell, the formula assistant should pop-up and it should show you all functions that start with that name.
If you do not see that pop-up, you have put your code in the wrong place.

no it does not work opened a new workbook and tried it in there also saved it as a micro enabeled workbook, wont show when typing it in

 

[Joe4]

Going back to an earlier question I asked:

What is the name of the Module where you put this User Defined Function in?