VBA to break word in two at a specfic symbol enter both words in cell [SOLVED]

[gauravkrgautam]

Hi all my senior,

i am confused very much, As related to earlier my post i got a cleaned code from Marc L in my last post which was compiling my 150 files but now tne thing is that

compiled files range begins from columnB and end at columnCY

What i have ==>

1.Files name format ==> CITY NAME(REGION) ==> which is current open file out of 150

2.I want to break above word in two parts "CITY NAME" & "REGION"

What i want ==>

1. "CITY NAME" in columnA accordingly

2. "REGION" in ColumnCZ accordingly


Sample Names

what i have ==>

Delhi(East)

Delhi(West)

Delhi(North)

Delhi(West)


what i want ==>

ColumnA......ColumnCZ

Delhi......East

Delhi......South

Delhi......North

Delhi......West


hope it is clear..


thanks in advance..

 

[Marc L]

Hi !

[pre]

Sub Example()
FName$ = "Miami (Florida) South East"

S& = InStr(FName, "(")

If S Then
E& = InStr(S, FName, ")")

If E Then
C$ = Trim$(Left$(FName, S - 1))
R$ = Mid$(FName, S + 1, E - S - 1)
MsgBox "City : " & C & vbLf & vbLf & "Region : " & R
End If
End If
End Sub

[/pre]

 

[gauravkrgautam]

Thanks Marc L this is working fantastically, msg box is appearing alike i want data in my columns.

being as new in vba i am unable to understand some characterstics in coding.

your guidance require to make it clear and to use it anywhere for future.

1.FName$ == We declare it as a string

2.S&, L& == ??? (please explain)


your guidance require, pls pardon me if i am going off track to topic.


thanks again Marc L

 

[Marc L]

You're welcome !

 

[list type=decimal]L& is equal to Dim L as Long …

If S Then is equal to If S <> 0 Then …[/list type=decimal]
 

Yes I know, I'm a bit lazy !

 

 

[shrivallabha]

You can do this without VBA also. Assuming your data is in column B.

=LEFT(B1,FIND("(",B1,1)-1)

will give Delhi and

=SUBSTITUTE(MID(B1,FIND("(",B1,1)+1,99),")","")

will give Region

On your data following will also work.

[pre]

Public Sub SplitData()
Dim r As Range
For Each r In Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
v = Split(Left(r.Value, Len(r.Value) - 1), "(")
Cells(r.Row, "A").Value = v(0)
Cells(r.Row, "CZ").Value = v(1)
Next
End Sub

[/pre]

 

[gauravkrgautam]

Thanks Marc L your laziness make me learnt two new things ...

and fairly as a selfish i would say ""its good to be lazy""...


Thanks Shrivallabha coding provided by you is working and also i have did little changes in it.


Thanks you both Mr. Shrivallabha and Mr. Marc L

my problem is resolved