Insert character in a cell using a keyboard shortcut

Hi,

I have a workbook with values in Column A that have to be split into
columns. Before splitting into columns I need to manually insert
delimiters eg. "~" . The user will get into the cell in Edit mode and
then use a keyboard shortcut to insert the pre-defined delimiter viz.
"~". Can this be done? (I tried recording a macro but the entire cell
string with the delimter was recorded.)

If this is not possible, any other way/ideas for inserting delimiters
where there is no pattern for programatically inserting the same?

Thanks in advance for the help.

Regards,
Rajendra
0
Raj
4/28/2010 2:21:25 PM
excel.programming 6508 articles. 2 followers. Follow

10 Replies
1097 Views

Similar Articles

[PageSpeed] 25

Is there any "rule" about where to place these delimiters (for example, 
after the 5th and 9th characters, after a date, etc.)? If so, we may be able 
to give you a macro that splits the text into columns automatically. I mean, 
if you are going to run code to perform the insert, you might as well run 
code to do all the work. Just give us as much information about the text 
that needs to be split and where the split(s) need to take place at.

-- 
Rick (MVP - Excel)



"Raj" <rspai9@gmail.com> wrote in message 
news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
> Hi,
>
> I have a workbook with values in Column A that have to be split into
> columns. Before splitting into columns I need to manually insert
> delimiters eg. "~" . The user will get into the cell in Edit mode and
> then use a keyboard shortcut to insert the pre-defined delimiter viz.
> "~". Can this be done? (I tried recording a macro but the entire cell
> string with the delimter was recorded.)
>
> If this is not possible, any other way/ideas for inserting delimiters
> where there is no pattern for programatically inserting the same?
>
> Thanks in advance for the help.
>
> Regards,
> Rajendra 

0
Rick
4/28/2010 4:50:44 PM
Hi,
in another column you can enter

="~"&A1
then copy this column on top the other as value

"Raj" wrote:

> Hi,
> 
> I have a workbook with values in Column A that have to be split into
> columns. Before splitting into columns I need to manually insert
> delimiters eg. "~" . The user will get into the cell in Edit mode and
> then use a keyboard shortcut to insert the pre-defined delimiter viz.
> "~". Can this be done? (I tried recording a macro but the entire cell
> string with the delimter was recorded.)
> 
> If this is not possible, any other way/ideas for inserting delimiters
> where there is no pattern for programatically inserting the same?
> 
> Thanks in advance for the help.
> 
> Regards,
> Rajendra
> .
> 
0
Utf
4/28/2010 5:14:02 PM
There is no obvious pattern for  splitting the string in Column 1. The
only way I could see was to manually insert delimiters. Hence, the
need.

Thanks and Regards,
Rajendra



On Apr 28, 9:50=A0pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Is there any "rule" about where to place these delimiters (for example,
> after the 5th and 9th characters, after a date, etc.)? If so, we may be a=
ble
> to give you a macro that splits the text into columns automatically. I me=
an,
> if you are going to run code to perform the insert, you might as well run
> code to do all the work. Just give us as much information about the text
> that needs to be split and where the split(s) need to take place at.
>
> --
> Rick (MVP - Excel)
>
> "Raj" <rsp...@gmail.com> wrote in message
>
> news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>
> > Hi,
>
> > I have a workbook with values in Column A that have to be split into
> > columns. Before splitting into columns I need to manually insert
> > delimiters eg. "~" . The user will get into the cell in Edit mode and
> > then use a keyboard shortcut to insert the pre-defined delimiter viz.
> > "~". Can this be done? (I tried recording a macro but the entire cell
> > string with the delimter was recorded.)
>
> > If this is not possible, any other way/ideas for inserting delimiters
> > where there is no pattern for programatically inserting the same?
>
> > Thanks in advance for the help.
>
> > Regards,
> > Rajendra

0
Raj
4/28/2010 5:25:36 PM
But how do you **know** where the delimiters are to be placed? What governs 
your decision where to place them? I'm sure you are not planning to close 
your eyes and blindly click within your text to place them? There must be 
some "rule" (no matter how thin or flimsy it is) governing where you are 
going to place the delimiters. Humor me... show us a few examples of the 
text you want to split and what they will look like after they are split.

-- 
Rick (MVP - Excel)



"Raj" <rspai9@gmail.com> wrote in message 
news:f23eca9a-ade7-4a3c-9823-38c5d3d79cdd@u30g2000prd.googlegroups.com...
> There is no obvious pattern for  splitting the string in Column 1. The
> only way I could see was to manually insert delimiters. Hence, the
> need.
>
> Thanks and Regards,
> Rajendra
>
>
>
> On Apr 28, 9:50 pm, "Rick Rothstein"
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> Is there any "rule" about where to place these delimiters (for example,
>> after the 5th and 9th characters, after a date, etc.)? If so, we may be 
>> able
>> to give you a macro that splits the text into columns automatically. I 
>> mean,
>> if you are going to run code to perform the insert, you might as well run
>> code to do all the work. Just give us as much information about the text
>> that needs to be split and where the split(s) need to take place at.
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Raj" <rsp...@gmail.com> wrote in message
>>
>> news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>>
>> > Hi,
>>
>> > I have a workbook with values in Column A that have to be split into
>> > columns. Before splitting into columns I need to manually insert
>> > delimiters eg. "~" . The user will get into the cell in Edit mode and
>> > then use a keyboard shortcut to insert the pre-defined delimiter viz.
>> > "~". Can this be done? (I tried recording a macro but the entire cell
>> > string with the delimter was recorded.)
>>
>> > If this is not possible, any other way/ideas for inserting delimiters
>> > where there is no pattern for programatically inserting the same?
>>
>> > Thanks in advance for the help.
>>
>> > Regards,
>> > Rajendra
> 
0
Rick
4/28/2010 5:35:04 PM
To elaborate, here's example data from column A:
KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
SP-17700IOOI
KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
A-3316776111

I need to split the string after the "384" for the first line, "938"
for the second line and "401" for the third line.

There is a pattern possibly solvable by use of regular expressions:.
"BBA followed by 11 numerical characters". In case the solution here
is a non Regex solution, then I am planning to post this seeking help
with a regex solution for the problem.

Regards,
Raj









On Apr 28, 10:25=A0pm, Raj <rsp...@gmail.com> wrote:
> There is no obvious pattern for =A0splitting the string in Column 1. The
> only way I could see was to manually insert delimiters. Hence, the
> need.
>
> Thanks and Regards,
> Rajendra
>
> On Apr 28, 9:50=A0pm, "Rick Rothstein"
>
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > Is there any "rule" about where to place these delimiters (for example,
> > after the 5th and 9th characters, after a date, etc.)? If so, we may be=
 able
> > to give you a macro that splits the text into columns automatically. I =
mean,
> > if you are going to run code to perform the insert, you might as well r=
un
> > code to do all the work. Just give us as much information about the tex=
t
> > that needs to be split and where the split(s) need to take place at.
>
> > --
> > Rick (MVP - Excel)
>
> > "Raj" <rsp...@gmail.com> wrote in message
>
> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com..=
..
>
> > > Hi,
>
> > > I have a workbook with values in Column A that have to be split into
> > > columns. Before splitting into columns I need to manually insert
> > > delimiters eg. "~" . The user will get into the cell in Edit mode and
> > > then use a keyboard shortcut to insert the pre-defined delimiter viz.
> > > "~". Can this be done? (I tried recording a macro but the entire cell
> > > string with the delimter was recorded.)
>
> > > If this is not possible, any other way/ideas for inserting delimiters
> > > where there is no pattern for programatically inserting the same?
>
> > > Thanks in advance for the help.
>
> > > Regards,
> > > Rajendra

0
Raj
4/28/2010 5:40:54 PM
It is hard to tell from your example because of word wrapping... is there a 
space after the numbers you want to split on? Also, from your examples, it 
looks like you want to lock in on the number after the second "hash mark" 
symbol (#)... is that the case?

-- 
Rick (MVP - Excel)



"Raj" <rspai9@gmail.com> wrote in message 
news:ecffd89b-e588-4abc-b547-8eefed198c8c@s36g2000prf.googlegroups.com...
> To elaborate, here's example data from column A:
> KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
> SP-17700IOOI
> KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
> KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
> A-3316776111
>
> I need to split the string after the "384" for the first line, "938"
> for the second line and "401" for the third line.
>
> There is a pattern possibly solvable by use of regular expressions:.
> "BBA followed by 11 numerical characters". In case the solution here
> is a non Regex solution, then I am planning to post this seeking help
> with a regex solution for the problem.
>
> Regards,
> Raj
>
>
>
>
>
>
>
>
>
> On Apr 28, 10:25 pm, Raj <rsp...@gmail.com> wrote:
>> There is no obvious pattern for  splitting the string in Column 1. The
>> only way I could see was to manually insert delimiters. Hence, the
>> need.
>>
>> Thanks and Regards,
>> Rajendra
>>
>> On Apr 28, 9:50 pm, "Rick Rothstein"
>>
>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> > Is there any "rule" about where to place these delimiters (for example,
>> > after the 5th and 9th characters, after a date, etc.)? If so, we may be 
>> > able
>> > to give you a macro that splits the text into columns automatically. I 
>> > mean,
>> > if you are going to run code to perform the insert, you might as well 
>> > run
>> > code to do all the work. Just give us as much information about the 
>> > text
>> > that needs to be split and where the split(s) need to take place at.
>>
>> > --
>> > Rick (MVP - Excel)
>>
>> > "Raj" <rsp...@gmail.com> wrote in message
>>
>> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>>
>> > > Hi,
>>
>> > > I have a workbook with values in Column A that have to be split into
>> > > columns. Before splitting into columns I need to manually insert
>> > > delimiters eg. "~" . The user will get into the cell in Edit mode and
>> > > then use a keyboard shortcut to insert the pre-defined delimiter viz.
>> > > "~". Can this be done? (I tried recording a macro but the entire cell
>> > > string with the delimter was recorded.)
>>
>> > > If this is not possible, any other way/ideas for inserting delimiters
>> > > where there is no pattern for programatically inserting the same?
>>
>> > > Thanks in advance for the help.
>>
>> > > Regards,
>> > > Rajendra
> 
0
Rick
4/28/2010 5:53:50 PM
I just noticed.. the "hash mark" (#) is not always the second one... but it 
does look like it is always the **last** one... is that the case? What about 
the dash and the space following it... are they always there? In other 
words, are you looking to split the text after the first number after the 
hash/dash/space characters?

-- 
Rick (MVP - Excel)



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:eSEgivv5KHA.980@TK2MSFTNGP04.phx.gbl...
> It is hard to tell from your example because of word wrapping... is there 
> a space after the numbers you want to split on? Also, from your examples, 
> it looks like you want to lock in on the number after the second "hash 
> mark" symbol (#)... is that the case?
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "Raj" <rspai9@gmail.com> wrote in message 
> news:ecffd89b-e588-4abc-b547-8eefed198c8c@s36g2000prf.googlegroups.com...
>> To elaborate, here's example data from column A:
>> KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
>> SP-17700IOOI
>> KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
>> KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
>> A-3316776111
>>
>> I need to split the string after the "384" for the first line, "938"
>> for the second line and "401" for the third line.
>>
>> There is a pattern possibly solvable by use of regular expressions:.
>> "BBA followed by 11 numerical characters". In case the solution here
>> is a non Regex solution, then I am planning to post this seeking help
>> with a regex solution for the problem.
>>
>> Regards,
>> Raj
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> On Apr 28, 10:25 pm, Raj <rsp...@gmail.com> wrote:
>>> There is no obvious pattern for  splitting the string in Column 1. The
>>> only way I could see was to manually insert delimiters. Hence, the
>>> need.
>>>
>>> Thanks and Regards,
>>> Rajendra
>>>
>>> On Apr 28, 9:50 pm, "Rick Rothstein"
>>>
>>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>>> > Is there any "rule" about where to place these delimiters (for 
>>> > example,
>>> > after the 5th and 9th characters, after a date, etc.)? If so, we may 
>>> > be able
>>> > to give you a macro that splits the text into columns automatically. I 
>>> > mean,
>>> > if you are going to run code to perform the insert, you might as well 
>>> > run
>>> > code to do all the work. Just give us as much information about the 
>>> > text
>>> > that needs to be split and where the split(s) need to take place at.
>>>
>>> > --
>>> > Rick (MVP - Excel)
>>>
>>> > "Raj" <rsp...@gmail.com> wrote in message
>>>
>>> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>>>
>>> > > Hi,
>>>
>>> > > I have a workbook with values in Column A that have to be split into
>>> > > columns. Before splitting into columns I need to manually insert
>>> > > delimiters eg. "~" . The user will get into the cell in Edit mode 
>>> > > and
>>> > > then use a keyboard shortcut to insert the pre-defined delimiter 
>>> > > viz.
>>> > > "~". Can this be done? (I tried recording a macro but the entire 
>>> > > cell
>>> > > string with the delimter was recorded.)
>>>
>>> > > If this is not possible, any other way/ideas for inserting 
>>> > > delimiters
>>> > > where there is no pattern for programatically inserting the same?
>>>
>>> > > Thanks in advance for the help.
>>>
>>> > > Regards,
>>> > > Rajendra
>> 
0
Rick
4/28/2010 6:25:26 PM
To be exactly precise, my last question should have read...

In other words, are you looking to split the text after the first number 
after the LAST hash/dash/space characters in the text (no matter how many 
hash/dash/space characters there are in total)?

-- 
Rick (MVP - Excel)



"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:uGdUMBw5KHA.5808@TK2MSFTNGP02.phx.gbl...
> I just noticed.. the "hash mark" (#) is not always the second one... but 
> it does look like it is always the **last** one... is that the case? What 
> about the dash and the space following it... are they always there? In 
> other words, are you looking to split the text after the first number 
> after the hash/dash/space characters?
>
> -- 
> Rick (MVP - Excel)
>
>
>
> "Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
> news:eSEgivv5KHA.980@TK2MSFTNGP04.phx.gbl...
>> It is hard to tell from your example because of word wrapping... is there 
>> a space after the numbers you want to split on? Also, from your examples, 
>> it looks like you want to lock in on the number after the second "hash 
>> mark" symbol (#)... is that the case?
>>
>> -- 
>> Rick (MVP - Excel)
>>
>>
>>
>> "Raj" <rspai9@gmail.com> wrote in message 
>> news:ecffd89b-e588-4abc-b547-8eefed198c8c@s36g2000prf.googlegroups.com...
>>> To elaborate, here's example data from column A:
>>> KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
>>> SP-17700IOOI
>>> KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
>>> KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
>>> A-3316776111
>>>
>>> I need to split the string after the "384" for the first line, "938"
>>> for the second line and "401" for the third line.
>>>
>>> There is a pattern possibly solvable by use of regular expressions:.
>>> "BBA followed by 11 numerical characters". In case the solution here
>>> is a non Regex solution, then I am planning to post this seeking help
>>> with a regex solution for the problem.
>>>
>>> Regards,
>>> Raj
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Apr 28, 10:25 pm, Raj <rsp...@gmail.com> wrote:
>>>> There is no obvious pattern for  splitting the string in Column 1. The
>>>> only way I could see was to manually insert delimiters. Hence, the
>>>> need.
>>>>
>>>> Thanks and Regards,
>>>> Rajendra
>>>>
>>>> On Apr 28, 9:50 pm, "Rick Rothstein"
>>>>
>>>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>>>> > Is there any "rule" about where to place these delimiters (for 
>>>> > example,
>>>> > after the 5th and 9th characters, after a date, etc.)? If so, we may 
>>>> > be able
>>>> > to give you a macro that splits the text into columns automatically. 
>>>> > I mean,
>>>> > if you are going to run code to perform the insert, you might as well 
>>>> > run
>>>> > code to do all the work. Just give us as much information about the 
>>>> > text
>>>> > that needs to be split and where the split(s) need to take place at.
>>>>
>>>> > --
>>>> > Rick (MVP - Excel)
>>>>
>>>> > "Raj" <rsp...@gmail.com> wrote in message
>>>>
>>>> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>>>>
>>>> > > Hi,
>>>>
>>>> > > I have a workbook with values in Column A that have to be split 
>>>> > > into
>>>> > > columns. Before splitting into columns I need to manually insert
>>>> > > delimiters eg. "~" . The user will get into the cell in Edit mode 
>>>> > > and
>>>> > > then use a keyboard shortcut to insert the pre-defined delimiter 
>>>> > > viz.
>>>> > > "~". Can this be done? (I tried recording a macro but the entire 
>>>> > > cell
>>>> > > string with the delimter was recorded.)
>>>>
>>>> > > If this is not possible, any other way/ideas for inserting 
>>>> > > delimiters
>>>> > > where there is no pattern for programatically inserting the same?
>>>>
>>>> > > Thanks in advance for the help.
>>>>
>>>> > > Regards,
>>>> > > Rajendra
>>> 
0
Rick
4/28/2010 6:36:56 PM
Thanks for your efforts in understanding the problem.

It would be more precise to include the "BBA" in determining the split
point.  This is because the string I have posted is part of a longer
string where the condition without the "BBA" could be met in other
parts. Therefore, the split point woud be "after the first number
after the last has/dash/space characters following the "BBA" in the
text.

Regards,
Raj





On Apr 28, 11:36=A0pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> To be exactly precise, my last question should have read...
>
> In other words, are you looking to split the text after the first number
> after the LAST hash/dash/space characters in the text (no matter how many
> hash/dash/space characters there are in total)?
>
> --
> Rick (MVP - Excel)
>
> "Rick Rothstein" <rick.newsNO.S...@NO.SPAMverizon.net> wrote in message
>
> news:uGdUMBw5KHA.5808@TK2MSFTNGP02.phx.gbl...
>
> > I just noticed.. the "hash mark" (#) is not always the second one... bu=
t
> > it does look like it is always the **last** one... is that the case? Wh=
at
> > about the dash and the space following it... are they always there? In
> > other words, are you looking to split the text after the first number
> > after the hash/dash/space characters?
>
> > --
> > Rick (MVP - Excel)
>
> > "Rick Rothstein" <rick.newsNO.S...@NO.SPAMverizon.net> wrote in message
> >news:eSEgivv5KHA.980@TK2MSFTNGP04.phx.gbl...
> >> It is hard to tell from your example because of word wrapping... is th=
ere
> >> a space after the numbers you want to split on? Also, from your exampl=
es,
> >> it looks like you want to lock in on the number after the second "hash
> >> mark" symbol (#)... is that the case?
>
> >> --
> >> Rick (MVP - Excel)
>
> >> "Raj" <rsp...@gmail.com> wrote in message
> >>news:ecffd89b-e588-4abc-b547-8eefed198c8c@s36g2000prf.googlegroups.com.=
...
> >>> To elaborate, here's example data from column A:
> >>> KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
> >>> SP-17700IOOI
> >>> KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
> >>> KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
> >>> A-3316776111
>
> >>> I need to split the string after the "384" for the first line, "938"
> >>> for the second line and "401" for the third line.
>
> >>> There is a pattern possibly solvable by use of regular expressions:.
> >>> "BBA followed by 11 numerical characters". In case the solution here
> >>> is a non Regex solution, then I am planning to post this seeking help
> >>> with a regex solution for the problem.
>
> >>> Regards,
> >>> Raj
>
> >>> On Apr 28, 10:25 pm, Raj <rsp...@gmail.com> wrote:
> >>>> There is no obvious pattern for =A0splitting the string in Column 1.=
 The
> >>>> only way I could see was to manually insert delimiters. Hence, the
> >>>> need.
>
> >>>> Thanks and Regards,
> >>>> Rajendra
>
> >>>> On Apr 28, 9:50 pm, "Rick Rothstein"
>
> >>>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> >>>> > Is there any "rule" about where to place these delimiters (for
> >>>> > example,
> >>>> > after the 5th and 9th characters, after a date, etc.)? If so, we m=
ay
> >>>> > be able
> >>>> > to give you a macro that splits the text into columns automaticall=
y.
> >>>> > I mean,
> >>>> > if you are going to run code to perform the insert, you might as w=
ell
> >>>> > run
> >>>> > code to do all the work. Just give us as much information about th=
e
> >>>> > text
> >>>> > that needs to be split and where the split(s) need to take place a=
t.
>
> >>>> > --
> >>>> > Rick (MVP - Excel)
>
> >>>> > "Raj" <rsp...@gmail.com> wrote in message
>
> >>>> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.=
com...
>
> >>>> > > Hi,
>
> >>>> > > I have a workbook with values in Column A that have to be split
> >>>> > > into
> >>>> > > columns. Before splitting into columns I need to manually insert
> >>>> > > delimiters eg. "~" . The user will get into the cell in Edit mod=
e
> >>>> > > and
> >>>> > > then use a keyboard shortcut to insert the pre-defined delimiter
> >>>> > > viz.
> >>>> > > "~". Can this be done? (I tried recording a macro but the entire
> >>>> > > cell
> >>>> > > string with the delimter was recorded.)
>
> >>>> > > If this is not possible, any other way/ideas for inserting
> >>>> > > delimiters
> >>>> > > where there is no pattern for programatically inserting the same=
?
>
> >>>> > > Thanks in advance for the help.
>
> >>>> > > Regards,
> >>>> > > Rajendra

0
Raj
4/29/2010 1:01:33 AM
I think this may do what you want... try it out and let me know. Select the 
column of cells you want to split apart and then run this macro...

Sub SplitCodes()
  Dim Cell As Range, Parts() As String, Number As String, CellTxt As String
  For Each Cell In Selection
    Parts = Split(Cell.Value, "BBA")
    Number = Val(Split(Parts(UBound(Parts)), "#- ")(1))
    CellTxt = Split(Parts(UBound(Parts)), Number)(1)
    Cell.Offset(, 1).Value = Trim(CellTxt)
    Cell.Value = Trim(Replace(Cell.Value, CellTxt, ""))
  Next
End Sub

-- 
Rick (MVP - Excel)



"Raj" <rspai9@gmail.com> wrote in message 
news:1cb72ecf-3f20-4590-b27d-fae5df188d69@a27g2000prj.googlegroups.com...
> Thanks for your efforts in understanding the problem.
>
> It would be more precise to include the "BBA" in determining the split
> point.  This is because the string I have posted is part of a longer
> string where the condition without the "BBA" could be met in other
> parts. Therefore, the split point woud be "after the first number
> after the last has/dash/space characters following the "BBA" in the
> text.
>
> Regards,
> Raj
>
>
>
>
>
> On Apr 28, 11:36 pm, "Rick Rothstein"
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> To be exactly precise, my last question should have read...
>>
>> In other words, are you looking to split the text after the first number
>> after the LAST hash/dash/space characters in the text (no matter how many
>> hash/dash/space characters there are in total)?
>>
>> --
>> Rick (MVP - Excel)
>>
>> "Rick Rothstein" <rick.newsNO.S...@NO.SPAMverizon.net> wrote in message
>>
>> news:uGdUMBw5KHA.5808@TK2MSFTNGP02.phx.gbl...
>>
>> > I just noticed.. the "hash mark" (#) is not always the second one... 
>> > but
>> > it does look like it is always the **last** one... is that the case? 
>> > What
>> > about the dash and the space following it... are they always there? In
>> > other words, are you looking to split the text after the first number
>> > after the hash/dash/space characters?
>>
>> > --
>> > Rick (MVP - Excel)
>>
>> > "Rick Rothstein" <rick.newsNO.S...@NO.SPAMverizon.net> wrote in message
>> >news:eSEgivv5KHA.980@TK2MSFTNGP04.phx.gbl...
>> >> It is hard to tell from your example because of word wrapping... is 
>> >> there
>> >> a space after the numbers you want to split on? Also, from your 
>> >> examples,
>> >> it looks like you want to lock in on the number after the second "hash
>> >> mark" symbol (#)... is that the case?
>>
>> >> --
>> >> Rick (MVP - Excel)
>>
>> >> "Raj" <rsp...@gmail.com> wrote in message
>> >>news:ecffd89b-e588-4abc-b547-8eefed198c8c@s36g2000prf.googlegroups.com...
>> >>> To elaborate, here's example data from column A:
>> >>> KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384
>> >>> SP-17700IOOI
>> >>> KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
>> >>> KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTI
>> >>> A-3316776111
>>
>> >>> I need to split the string after the "384" for the first line, "938"
>> >>> for the second line and "401" for the third line.
>>
>> >>> There is a pattern possibly solvable by use of regular expressions:.
>> >>> "BBA followed by 11 numerical characters". In case the solution here
>> >>> is a non Regex solution, then I am planning to post this seeking help
>> >>> with a regex solution for the problem.
>>
>> >>> Regards,
>> >>> Raj
>>
>> >>> On Apr 28, 10:25 pm, Raj <rsp...@gmail.com> wrote:
>> >>>> There is no obvious pattern for  splitting the string in Column 1. 
>> >>>> The
>> >>>> only way I could see was to manually insert delimiters. Hence, the
>> >>>> need.
>>
>> >>>> Thanks and Regards,
>> >>>> Rajendra
>>
>> >>>> On Apr 28, 9:50 pm, "Rick Rothstein"
>>
>> >>>> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>> >>>> > Is there any "rule" about where to place these delimiters (for
>> >>>> > example,
>> >>>> > after the 5th and 9th characters, after a date, etc.)? If so, we 
>> >>>> > may
>> >>>> > be able
>> >>>> > to give you a macro that splits the text into columns 
>> >>>> > automatically.
>> >>>> > I mean,
>> >>>> > if you are going to run code to perform the insert, you might as 
>> >>>> > well
>> >>>> > run
>> >>>> > code to do all the work. Just give us as much information about 
>> >>>> > the
>> >>>> > text
>> >>>> > that needs to be split and where the split(s) need to take place 
>> >>>> > at.
>>
>> >>>> > --
>> >>>> > Rick (MVP - Excel)
>>
>> >>>> > "Raj" <rsp...@gmail.com> wrote in message
>>
>> >>>> >news:a757e539-eb5b-49f4-9961-739f10b2fe67@s13g2000prc.googlegroups.com...
>>
>> >>>> > > Hi,
>>
>> >>>> > > I have a workbook with values in Column A that have to be split
>> >>>> > > into
>> >>>> > > columns. Before splitting into columns I need to manually insert
>> >>>> > > delimiters eg. "~" . The user will get into the cell in Edit 
>> >>>> > > mode
>> >>>> > > and
>> >>>> > > then use a keyboard shortcut to insert the pre-defined delimiter
>> >>>> > > viz.
>> >>>> > > "~". Can this be done? (I tried recording a macro but the entire
>> >>>> > > cell
>> >>>> > > string with the delimter was recorded.)
>>
>> >>>> > > If this is not possible, any other way/ideas for inserting
>> >>>> > > delimiters
>> >>>> > > where there is no pattern for programatically inserting the 
>> >>>> > > same?
>>
>> >>>> > > Thanks in advance for the help.
>>
>> >>>> > > Regards,
>> >>>> > > Rajendra
> 
0
Rick
4/29/2010 2:30:34 AM
Reply:

Similar Artilces:

outlook not using new GAL
I didn't hear back after a while for a previous post, so I'm going to try again. We built a new GAL that we want users to use which filters out some accounts that we don't people to see. We applied this GAL to the new OAB and set the server to use this new OAB. Then we rebuilt it. Anyone who creates a new Outlook profile gets this NEW GAL/OAB, but anyone who keeps using their current Outlook profile keeps being able to see the new hidden accounts. We tried manually deleting the downloaded OAB files, redownloading, etc. Nothing has worked. We are on exchange 2003 sp2 and u...

Suppres Zero or empty Cell value in a line graph
Hi I'm using Office 2007. I have two charts using data from the same sheet. The second graph is a copy of the first. In the first graph, the empty and zero value cells are not displayed. In the second graph, the zero value cells is displayed (draged to zero) although the option "connect points with line" is checked. The strange thing: if I change the Y-data to another part of the sheet, it is no longer dragged to zero. Even if the cell is empty it's still dragged to zero A formula that returns "" is not an empty cell, it's a formula (or it's a small ...

how do I add a 'web address' to a cell in excel with a hyperlink?
how do I add a 'web address' to a cell in excel with a hyperlink? I am a new student to computers, and part of a test is to insert a hyperlink to a cell in excel linking to a web address. I have searched computer for any 'Hyperlink' connection words, but I think it not on here, Is that possible, although I have top of range computer. Anyone with a solution? I be ever so greatful. in xl2002 ctrl+k>in the link to select existing web page>either type in (at the bottom) or point to the url on your browser>enter -- Don Guillett SalesAid Software dguillett1@austi...

Using Building Blocks, Content Controls and XMLMappings to generate multi-page template-generated sections in a Word Document
Dear Readers, I would like some of your insight and feedback regarding a document I am creating. Here is the scenario. The document is a package that describes a toy collection. There is supposed to be a section in the document which consists of a sequence of pages that repeat an identical one-page template for each item in the collection. I have an XML document (Excel Table mapped export) which describes each item in the collection, and I would like to programmatically generate these pages of the document from the content in the XML file and the presentation in the template. S...

Linked Cell Property In Activex controls
Can someone point me to an example showing how this property can be used, linking, as an example, an option button to a specific cell? Say if I wanted "1" to appear in cell B2 of the worksheet if the option button is clicked KG, The option button puts TRUE or FALSE into its Linked Cell. You can get 1 or 0 out of that by referring to it with double negation operators. = --A1 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" <KG@discussions.microsoft.com> wrote in message news:924E6B40-6040-4016-B75B...

Removing Blank Cells #3
I would like to know if there is a way to remove the blank columns and rows from a worksheet. There really is no need for this many blank rows in the workpaper I am working on and I assume it contributes to the overall size of the spreadsheet. Thanks in advance. Hi you can't remove them (you can hide them if you like9. Also they do not add up to the size of your worksheet 8at least as long as your used range is correct) -- Regards Frank Kabel Frankfurt, Germany Jeff Mortenson wrote: > I would like to know if there is a way to remove the blank > columns and rows from a works...

Search folder criteria
I am using Outlook 2007. How can I customise my search folder to filter messages that contain "KSC" in subject OR belong to "Sports" category? Is there a reason why "KSC" cannot not be assigned "Sports" catagory? Melissa wrote: > I am using Outlook 2007. > How can I customise my search folder to filter messages that contain "KSC" > in subject OR belong to "Sports" category? ...

Display Sheetname in a cell
I'd like to display a worksheet name "with the body" instead of "in the margins" (ie, using Header / Footer definitions). Is there a built-in mechanism for picking up the worksheet name in a formula? I tried putting Function GetSheetName() As String GetSheetName = ActiveSheet.Name End Function in a code module and " = GetSheetName() " in a cell; but quickly discovered that <F9> doesn't cause the user defined function to re-evaluate. Suggestions? -- Clif Try this: =MID(CELL("filename",A1),FIND("]",CELL("fil...

Hyperlink directly to a cell in a worksheet
Hi there, I'd like to hyperlink directly to a cell in a spreadsheet (spreadsheet opens to a specific cell selected). The link I'm using opens the spreadsheet, but it goes to the same cell everytime. Is there a way to do this? Thanks in advance for any help. Here's what the link I'm using looks like: www.myserver.com/Payroll_Risks_&_Controls.xls#='Risk Scores 1, 2, or 3'!$A$27 Risk Scores 1, 2, or 3 is the name of the worksheet and A27 is the cell I want to go to. ...

drag and drop problem with cell references
i have a cell with a formula in one worksheet that references another cell. the formula looks like this: =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25) where 'draft' is another worksheet in my workbook. the formula works fine, it looks empty if there is nothing in the referenced cell('Draft'!$D25), otherwise it mirrors the content of that cell. If i type something in the referenced cell('Draft'!$D25), it still works great. But if i drag something to the referenced cell than the formula gets screwed up and replaces the $D25, with #ref. I am gue...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Can I insert multiple scales on the Y axis of a single XY graph?
I am trying to plot three parameters on a single XY line chart. Is it possible io have three different scales charted to cover all the three parameters which are quite variable numerically? Using Excel 2000 No, you can only have a left and a right y-axis. Workaround: let's assume one series has very small numbers; have a column with these values multiplied by say 1000; plot this column and use a legend like "voltage x 1000" If one series is too large, have a column with the numbers divided; legend "Sales in $million) best wishes -- Bernard V Liengme www.stfx.ca/people...

Can you use the old Lotus "/ commands" in Excel 2003?
In Excel 2000 you could use a demo mode to activate the old Lotus menu commands. Can you do this in Excel 2003? Look at Tools>Option>Transition -- Kind regards, Niek Otten "DAW" <DAW@discussions.microsoft.com> wrote in message news:B5590E6E-DFE1-4A9A-8651-387B7F49D112@microsoft.com... > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do this in Excel 2003? > It's gone from xl2003. DAW wrote: > > In Excel 2000 you could use a demo mode to activate the old Lotus menu > commands. Can you do thi...

Need help removing characters in multiple cells
I have an excel 2007 sheet that has over 300 cells in a column that have a name of a store plus a number between paranethesis. Is there a one step way I can remove the paranethesis? Here is an example: K-Mart (ABC#73846365) and I want to have just the following K-Mart Each cell has a different store name and set of numbers within the paranethesis. Thanks Select the range to fix. Edit|replace what: _(* (spacebar, open paren, asterisk) with: (leave blank) replace all It looked like you'd want to remove the space character after the t in k-mart. "<----- Mardm...

Uses for Screen OCR technology
I have technology that is similar to OCR technology except that it determines rather than estimates its results. The main advantage over conventional OCR technology is that it is much more accurate with very small fonts. Current testing indicates that it can achieve 100% accuracy with fonts as small as 6 point. It also works with both types of font smoothing. I have thought of a few different uses for this technology, I want to see if anyone here can come up with any more uses. -- Patented SeeScreen enables programs to see anything on the computer display screen www.SeeScreen.com ...

remember path when linking many GIFs to cells?
When I link a number of GIF images residing in a different directory to Excel 2002 cells, the program does not remember the last directory it looked in when a new link is entered. Is there a way to set up a default path for this boring operation? Thanks, z.entropic ...

How do I change the format of how my active cell in Excel is view.
I sometimes have trouble locating which cell is active in my Excel worksheet. I would like to be able to change it to something other than just a heavy black border. Here is one way that highlights the row and column of the activecell. Private Sub WorkSheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add T...

how do I insert sequential numbering on a sheet of 10 pages?
-- Morrell http://www.publishermvps.com/Default.aspx?tabid=95 -- Ed Bennett - MVP Microsoft Publisher ...

using Outlook on local machine with domain user account
I have a small network running with w2k domain. Client computers run under w2k pro and have Office 2000 installed. * When I log on locally (with administrative privileges) Outlook works fine. * When I log on to the domain (using any domain user account) Outlook does not start. * When I add a domain user account to local administrators group and log on to the domain Outlook works fine. How to make Outlook working when domain user account is used (without administrative privileges on local machine) or I must use an account that has administrative privileges on local machine to run Outlook? ...

Allow a contact to use its Account's addresses
Since contacts associated to an Account all use the same addresses, allow a contact to use its Account's addresses ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=9c809...

Using Trebuchet MS Font : Bold and Italic not in Font Dialog
I've read that you should use the actual "bold variant" family of a font instead of just choosing "bold" (because the latter would just make it heavier and you might lose some definition/character). 1. Is this true? 2. When In use Trebuchet MS in Publisher or Word 2003 they don't show the bold or italic versions that are in the Font folder (Trebuchet MS Bold,etc.) Do they just automagically use the bold family when you bold something? And, if so (BONUS QUESTION) : is this better, the same, or worse "looking" than a font with a special different f...

make cell show blank when total is zero
Here is my formula: =COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),"*" & $B8 & "*") When the total is zero, how do I show the cell as blank instead of having a 0 show in the cell? Hi you could use a custom format such as 0.0;-0.0; -- Regards Frank Kabel Frankfurt, Germany "jtinne" <jtinne@discussions.microsoft.com> schrieb im Newsbeitrag news:0B58C97D-34AF-43FA-AFB7-51C90ABD3363@microsoft.com... > Here is my formula: > =COUNTIF(OFFSET('Daily MP'!$A:$A,0,MATCH(H$3,'Daily MP'!$5:$5,0)-1),&...

OT: If you use a lot of hotkeys, bookmark this site
If you are like me and use a lot of Hotkeys, this is a kick butt website!!! I thought I knew a lot of shortcuts but this place is amazing. Gotta bookmark it. http://allhotkeys.com/ -- Ok, I admit it, I killed Barney!! http://www.lockergnome.com/darksentinel You know what to do with the munge DarkSentinel <darkmungesentinel@munge.charter.munge.net> wrote: > If you are like me <snip> No, I'm not like you - I don't spam newsgroups. Reported. From: "DarkSentinel" <darkmungesentinel@munge.charter.munge.net> Newsgroups: microsoft.public.outlook Subje...

Losing Hyperlink's when using mail merge
Look for a fix to outlook stripping Hyperlink's form the body of an email. AFAIK, you'll need a third party mass mailing app, then - mail merge uses only plain text, if I recall correctly. John C wrote: > Look for a fix to outlook stripping Hyperlink's form the > body of an email. ...

what control should I use to display a bmp file?
Hello everybody, Like in subject: what control should I use to display a bmp file? I've created a dialog with 'picture control', but can't seem to find any way to display a bitmap on it having path given. It's not one, special bitmap. It's user-defined, selected from 'Open file' dialog. What method should I use to display a bmp on a 'picture control'? Any ideas? The following tutorial shows one way to load a bitmap. http://functionx.com/MFCFundamentals/Lesson11.htm -- Cheers Check Abdoul [VC++ MVP] --------------------------------...