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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
310
huiettcm
H
Jaafar Tribak
  • Question
Multi-Color VBA MsgBox Including Buttons ( Easy to use )
2
Replies
15
Views
4K
triettriet
T
Jaafar Tribak
  • Question
MultiColor + Drag N Drop ListBox Class (Win32)
Replies
8
Views
2K
Hasson
H
S
  • Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
219
Domenic
D
Jaafar Tribak
  • Question
Date Input Mask ( Applying a Watermark to TextBox)
Replies
9
Views
2K
Davmacrat
Davmacrat

Forum statistics

Threads
1,215,110
Messages
6,123,139
Members
449,098
Latest member
Doanvanhieu

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