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

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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

Insert character in a cell using a keyboard shortcut
 

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
.

Insert character in a cell using a keyboard shortcut
 

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"
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" wrote in message

...

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

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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"
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" wrote in message

...

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

Insert character in a cell using a keyboard shortcut
 

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 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"

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" wrote in message

....

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

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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 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"

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" wrote in message

...

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

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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" wrote in message
...
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 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"

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" wrote in message

...

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

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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 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"

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" wrote in message

...

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

Insert character in a cell using a keyboard shortcut
 

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"
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" wrote in message

...

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" wrote in message
...
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" wrote in message
....
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 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"

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.

Insert character in a cell using a keyboard shortcut
 

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" wrote in message
...
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"
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" wrote in message

...

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" wrote in message
...
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" wrote in message
...
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 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"

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" wrote in message

...

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