Function to split a string into multiple characters

Question

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

A VBA user-defined-function can't change the values of other cells. Maybe you can use a WorkSheet_Change event instead, or just keep the original value in column A and use Excel formulas in other columns to hold the separate letters. As a side note, try using the `Mid` function, instead of `Left` and `Right`. — Doug Glancy, Feb 08 '14 at 22:23

score 5 · Answer 1 · answered Nov 21 '16 at 11:56

5

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

answered Nov 21 '16 at 11:56

rbhattad

51
1
1

score 2 · Answer 2 · answered Feb 08 '14 at 23:49

2

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))

answered Feb 08 '14 at 23:49

Peter Albert

16,917
5
64
88

score 2 · Answer 3 · edited Sep 12 '14 at 07:30

2

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

edited Sep 12 '14 at 07:30

ivan.sim

8,972
8
47
63

answered Sep 12 '14 at 07:00

EMAD

21
1

score 1 · Answer 4 · answered Feb 05 '20 at 13:58

1

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)

answered Feb 05 '20 at 13:58

Gabriele

11
2

1

This seems to be the same answer @rbhattad gave. And I would recommend refraining from this profuse usage of images outside of SO. – jBuchholz Feb 05 '20 at 14:17
@Scorpioo590 Although I agree 100% with your opinion that this is a 'duplicate' answer, please bear in mind the "i.stack.imgur.com" is the site that SO uses for both linked and embedded images. – Adrian Mole Feb 05 '20 at 14:21

Dmitry Pavliv · Answer 5 · 2014-02-08T22:29:53.597

0

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:

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

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

edited Feb 08 '14 at 22:29

answered Feb 08 '14 at 22:24

Dmitry Pavliv

35,333
13
79
80

not really working it renders the 1st letter only unfortunatly – user3288319 Feb 08 '14 at 22:34
is there any way to get a variable inside a function to be a global variable ? – user3288319 Feb 08 '14 at 22:35
1

have you selected B1:F1 and pressed CTRL+SHIFT+ENTER as I wrote? – Dmitry Pavliv Feb 08 '14 at 22:35
works perfect .. but i don't wanna select anything because i have a list of words and each word contains a different number of letters so it wouldn't be useful to do the same for all words ! – user3288319 Feb 08 '14 at 22:43
should it be necessary user defined function or macro will fit too? – Dmitry Pavliv Feb 08 '14 at 22:48
i was hoping i could make a function but .. ok i will try a macro then thanks – user3288319 Feb 08 '14 at 22:51
if you'd like to use UDF, [here is one trick that can help you](http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change/8711582#8711582), (see @brettdj 's answer ) – Dmitry Pavliv Feb 08 '14 at 22:54

score 0 · Answer 6 · answered Oct 09 '17 at 12:54

0

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

answered Oct 09 '17 at 12:54

katie lu

489
1
5
23

score 0 · Answer 7 · answered Apr 28 '23 at 13:33

0

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)

answered Apr 28 '23 at 13:33

user21764603

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 02 '23 at 21:00

Function to split a string into multiple characters

7 Answers7