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

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

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

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

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

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

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

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
.