CODEWITHSUNDEEP
vbaexcel
user3288319
user3288319

Reputation: 21

Function to split a string into multiple characters

I want to create a custom function that takes the selected parameter and splits its content on different cells.

example :

A1=ABCDE

becomes

B1=A, C1=B, D1=C, E1=D, F1=E

so this is what I tried : 

Function SplitWord(Word)
NbCar = Len(Word) // get the number of cardinals of the text
SplitWord = Left(Word, 1) // put the first letter in the cell that called the function
t = NbCar - 1
For i = 1 To t
ActiveCell.Offset(0, i) = Right(Left(Word, i), 1)
Next
End Function

Upvotes: 2

Views: 30393

Answers (7)

user21764603
user21764603

Reputation: 21

I'm digging up a bit this subject but I have found a solution today as we now have the SEQUENCE formula: =MID(A1,SEQUENCE(1,LEN(A1),1,1),1)

Upvotes: 2

Gabriele
Gabriele

Reputation: 11

1) Place text you want to split in cell A1 (image)

2) Then paste this function into any cell you want (image)

=MID($A1;COLUMN(A1)-COLUMN($A1)+1;1)

3) Move the mouse cursor over the little block in the lower right corner of the cell in which you pasted the above-mentioned function (image)

4) Click and hold on the fill handle, and drag to the right to fill in the series (image)

Upvotes: 1

katie lu
katie lu

Reputation: 499

Place this in any first row cell: =MID($A$1,ROW(),1)

Upvotes: 0

rbhattad
rbhattad

Reputation: 51

  1. Enter the contents to split in cell A1.
  2. Paste this in cell B1: =MID($A$1,COLUMN()-COLUMN($B$1)+1,1)
  3. Drag to the right

Upvotes: 5

EMAD
EMAD

Reputation: 21

Try =MID($A1,COLUMNS($A$1:A$1),1), then drag it left it shall come.

Upvotes: 2

Peter Albert
Peter Albert

Reputation: 17515

You could also do this with simple Excel formulas - place this cell in B1 and copy it to C1-F1:

=MID(A1,COLUMN()-COLUMN($B$1)+1,1))

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

You can't modify cells in UDF, when calling this UDF from sheet (actually there is possible ways, but they're sophisticated and I don't recomend to use them). You can use this funciton instead:

Function SplitWord(Word As String) As String()
    Dim res() As String
    ReDim res(1 To Len(Word))
    For i = 1 To Len(Word)
        res(i) = Mid(Word, i, 1)
    Next
    SplitWord = res
End Function

How to use it:

  1. Select destination range (e.g. B1:F1)
  2. With selected range enter formula in formula bar =SplitWord(A1)
  3. Press CTRL+SHIFT+ENTER to evaluate it

If your destination range in one column (e.g. B1:B5) use =TRANSPOSE(SplitWord(A1))

Upvotes: 0

Related Questions