Insert character in a cell using a keyboard shortcut

[Raj]

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

 

[Rick Rothstein]

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.

 

[Eduardo]

Hi,
in another column you can enter

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

 

[Raj]

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

 

[Rick Rothstein]

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.

 

[Raj]

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

 

[Rick Rothstein]

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 Rothstein]

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 Rothstein]

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)?

 

[Raj]

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

 

[Rick Rothstein]

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