Text Manipulation Assistance

R

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
87
Office Version
  1. 365
Platform
  1. Windows
Could someone help me achieve something similar to my below example?

I'd like to be able to take the text keyed into the sample text area, and have each cell in a column enter one character of the text. That way, I can concatenate the strings. Is there a way to do this?

Minecraft Planning2.xlsx
BCDE
2Sample Text
3This is a sample This is a sample
4
5String StartSingle LetterString EndConcatenated
6<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
7<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
8<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
9<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
10<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
11<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
12<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
13<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
14<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
15<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
16<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
17<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
18<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
19<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
20<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
21<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
22<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
23<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" />
24<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
25<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" />
26<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
27<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
28<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
29<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
30<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
31<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
32<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
33<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
34<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
35<Action type="2" message="0" wParam="42024" lParam="0" sParam="" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
36<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
37<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
38<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
39<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
40<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
41<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
42<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
43<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" />
44<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
45<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A;" />
Sheet3
Cell Formulas
RangeFormula
E6:E45E6=CONCATENATE(B6,C6,D6)
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
something like this:
MrExcel Playbook 02 2021-09.xlsm
BCDE
2Sample Text
3This is a sample This is a sample
4
5String StartSingle LetterString EndConcatenated
6<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
7<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
8<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
9<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
10<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
11<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
12<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
13<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
14<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
15<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
16<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
17<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
18<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
19<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
20<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
21<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
22<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
23<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
24<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
25<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
26<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
27<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
28<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
29<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
30<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
31<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
32<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
33<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
34<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
35<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
36<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
37<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
38<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
39<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" /><Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
40<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
41<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
42<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
43<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
44<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
45<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " /><Action type="2" message="0" wParam="42024" lParam="0" sParam="" />
Sheet2
Cell Formulas
RangeFormula
C6:C45C6=MID($B$3,ROW()-5,1)
E6:E45E6=CONCATENATE(B6,C6,D6)
 
Upvote 0
aaaah, apart from the special characters.
Do you need both CR & LF ?
 
Upvote 0
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Change the formula in column C to this:
Excel Formula: 
=SUBSTITUTE(SUBSTITUTE(MID($B$3,ROW()-5,1),CHAR(10),"&#x000A;"),CHAR(13),"&#x000D;")
 
Upvote 0
1. It is unclear exactly what is in B3 in your sample.
a. Ref bobsan42's comments re CR & LF.
b. When I copy the mini-sheet, there is nothing in B3 following the second "sample" yet your results appear to indicate that there is more.

2. Can you confirm that when a space character appears in B3, you do not want a space in column C (or column E) at that position?
 
Upvote 0
Is it possible that XL2BB transfers the CR+LF combination as LF only?
I believe the "missing" spaces to be a mistake.
 
Upvote 0
Interesting how XL2BB parsed that out. What is actually missing is:
Peter_SSs said:
1. It is unclear exactly what is in B3 in your sample.
a. Ref bobsan42's comments re CR & LF.
b. When I copy the mini-sheet, there is nothing in B3 following the second "sample" yet your results appear to indicate that there is more.

2. Can you confirm that when a space character appears in B3, you do not want a space in column C (or column E) at that position?
Click to expand...

A space in B3 would need to concatenate (Column E) to " " and I believe I need CR+LF. Sample text is where I would type any block of text

For example, if in B3 I typed

1
2

4

I'd like to have, beginning in C6

1
&#x000D;
&#x000A;
2
&#x000D;
&#x000A;
&#x000D;
&#x000A;
4
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jaafar Tribak
  • Question Question
KeyPress Event for Worksheet Cells (Subclassing Without Crashing)
Replies
0
Views
229
Jaafar Tribak
Jaafar Tribak
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
748
huiettcm
H
Jaafar Tribak
  • Question Question
Capturing the Paste Event (API Hooking - Redirection)
Replies
4
Views
771
Jaafar Tribak
Jaafar Tribak
Jaafar Tribak
  • Question Question
Multi-Color VBA MsgBox Including Buttons ( Easy to use )
2
Replies
15
Views
6K
triettriet
T
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
288
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,714
Messages
6,174,052
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top