\Want to separate words

**Kamal Asharaf** · 06-12-2012, 07:02 AM

Hello Friends,
I have some query regarding how to separate a words like this "CurrentAssetsLongTerm" as Current Assets Long Term. Since there are no spaces or semi colon, am unable to seperate them. I need to used vlook in the seperated text , so if any of you may help, it would be my pleasure. Could not attempt manually as there are more than 20,000 words like them? Kindly help and revert ASAP.

Regards,
Kamal

**pike** · 06-12-2012, 07:21 AM

Hi

Maybe a UDF

Option Explicit
Function Replace_Words(Myrange As String)
    Dim RegEx  As Object, RegMatchCollection As Object, RegMatch As Object, OutPutStr As String
    Replace_Words = Myrange
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "([A-Z][a-z]+)"
        If .Test(Myrange) Then
        Set RegMatchCollection = .Execute(Myrange)
        For Each RegMatch In RegMatchCollection
            OutPutStr = OutPutStr & " " & RegMatch
        Next
        Replace_Words = OutPutStr
         End If
        End With
    Set RegEx = Nothing
End Function

**pike** · 06-12-2012, 07:25 AM

or even this pattern

.Pattern = "([A-Z^\s][a-z]+)"

**Kamal Asharaf** · 06-12-2012, 07:46 AM

Thanks for replying , but the problem is that i dont understand VBA.....Is there any other way out to seperate the words by general excel given formula or group of formulas.

Regards,

Kamal

**pike** · 06-12-2012, 08:04 AM

you can add the UDF by pressing "Alt" + "F11" to open the visual basic editor
add a code module and past the code into it .. its not hard

**Kamal Asharaf** · 06-12-2012, 08:09 AM

am Attaching a sample file , kindly revert with the formula or even VBA code with instruction as how to change the range of data .

**Md Aejaz** · 06-12-2012, 08:24 AM

Hi,

Help in excel required..xlsm
This is the file having the code posted as above

This sheet contains the function which will work as other function only u have to pass a input reference.
if u want to use this function in your system regularly .

create the copy of this file and then save it with any name but having an extention .xlam .and open it and start using this function.
this will create a addin where u r not required to copy paste this Code again and again.

Thanks

**Kamal Asharaf** · 06-12-2012, 08:46 AM

thanks ! Thanks a ton. Got it ......Hats off for you .

**pike** · 06-12-2012, 08:23 AM

A3
=MID(A3,MATCH(TRUE,EXACT(MID(A3,ROW($1:$28),1),PROPER(MID(A3,ROW($1:$28),1))),0),MATCH(TRUE,EXACT(MID(A3,ROW($2:$28),1),PROPER(MID(A3,ROW($2:$28),1))),0))&" "&MID(A3,MATCH(TRUE,EXACT(MID(A3,ROW($2:$28),1),PROPER(MID(A3,ROW($2:$28),1))),0)+1,255)
entered with Control + shift + enter
its not finnished but time to retire for the night

**twiggywales** · 06-12-2012, 08:48 AM

are they all started with capital leters?

**Kamal Asharaf** · 06-12-2012, 08:53 AM

yes ! Thanks Again .

\Want to separate words

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

\Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Re: \Want to separate words

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions