Textsplit & Concatenate from one column to another / multiple rows.

[RoosterBull]

Hello all that read this
I am trying to write a VBA code but without any success, and hoping that a good samaritan out there can help.
I have many strings of text, that within are divided by a "#" in column V / multiple rows. The end result that I am looking for it that each string of text be placed in Column F - Respective row, but with the text in sort of bullet form.
I have managed to do a code with VBA......albeit copying a recorded VBA but any cell in Column V that contains no text then produces a "#VALUE!" that I dont want.
The text in Row1 must be unaffected as it is static titles.
Very difficult to describe with words, so I have attached an example of what I have to Start and what the final objective is.
Please help if you can, it will be most appreciated.

 

[hagia_sofia]

Hello, would this work for you?

=SUBSTITUTE(V2:V5,"#",CHAR(10))

 

[RoosterBull]

Hello, would this work for you?

=SUBSTITUTE(V2:V5,"#",CHAR(10))

Hi Hagia_Sofia
Thank you for your reply, but no. I am looking for a VBA code to do the operation, not an excel formula.
Regards RB

 

[kvsrinivasamurthy]

VBA code.

Sub FillColumnF()
Dim Lr&, T&
Dim A, M
Lr = Range("V" & Rows.Count).End(xlUp).Row
A = Range("V2:V" & Lr)
ReDim R(1 To UBound(A, 1), 1 To 1)
For T = 1 To UBound(A, 1)
If A(T, 1) <> "" Then
M = Split(A(T, 1), "#")
R(T, 1) = Join(M, Chr(10))
M = ""
End If
Next T
Range("F2").Resize(UBound(A, 1), 1) = R
End Sub

 

[RoosterBull]

Hi Kvsrinivasamurthy, and thank you very much for your time in helping. Your code works perfectly for my application. Thank you

 

[kvsrinivasamurthy]

Glad that problem soled. You are welcome.