Text split formula

[Jagdev Singh]

Hi Experts
Could you please help me with the excel formula to split the below string from Right
I tried using the feature Text to column function but I need the entire text to divide in three columns only, The last word in the third column, second last in 2nd column and rest in 1st column.
Please refer the sample file for your reference.
Regards,
JD

 

[Deepak]

This is little ugly but working....

Add1: =LEFT(A2,LEN(A2)-LEN(C2)-LEN(D2)-4)
Add2: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",99)),(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))-1)*99,99))
Add3: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

 

[Hui]

JD

Try this slightly modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
   
Dim rwCount As Integer
Dim i As Integer
Dim vA() As String

Application.EnableEvents = False

rwCount = Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To rwCount
  vA = Split(Cells(i, 1).Text, "-")
  Cells(i, 2) = vA(0)
  Cells(i, 3) = vA(1)
  Next
Application.EnableEvents = True

End Sub

 

[Deepak]

@Hui This might solution for some other thread.

 

[Jagdev Singh]

Hi Deepak

Thanks for the formula. It just worked like a charm.

Regards,
JD

 

[Jagdev Singh]

Hi Deepak

Could you please help mw understand the below formula.

Add3: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

Regards,
JD

 

[Deepak]

Hi Deepak

Could you please help mw understand the below formula.

Add3: =TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

Regards,
JD

As I am reading the post on mobile.so will explain you on laptop meanwhile you would try formula evaluation to understand the same.

One thing more:

Your name JD sound jagga daku like as movie houseful 2.

 

[Jagdev Singh]

Thanks for reminding me about the movie. My friend called me JD as a short form of my name!

Regards,
JD

 

[Deepak]

Hi JD,

TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",99)),99))

First it will replace "," with 99 space as "........., London,, Uk" will get converted to ".........
Uk
Then right function will extract last 99 chars from it as " Uk".

& finally trim will remove the extra spaces.

 

[Jagdev Singh]

Nice concept Deepak! thanks