Text Manipulation Assistance

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)
 
Thanks for updating your profile. (y)

What about this in E6 (no need for columns A:C)?

reaktorblue.xlsm
BCDE
3This is a sample This is a sample
4
5
6<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
7<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
8<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
9<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
10<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
11<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
12<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
13<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
14<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
15<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
16<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
17<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
18<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
19<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
20<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
21<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
22<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
23<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A" />
24<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
25<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A" />
26<Action type="2" message="0" wParam="42024" lParam="0" sParam="T" />
27<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
28<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
29<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
30<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
31<Action type="2" message="0" wParam="42024" lParam="0" sParam="i" />
32<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
33<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
34<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
35<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
36<Action type="2" message="0" wParam="42024" lParam="0" sParam="s" />
37<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
38<Action type="2" message="0" wParam="42024" lParam="0" sParam="m" />
39<Action type="2" message="0" wParam="42024" lParam="0" sParam="p" />
40<Action type="2" message="0" wParam="42024" lParam="0" sParam="l" />
41<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
42
Sheet2
Cell Formulas
RangeFormula
E6:E41E6=LET(lf,CHAR(10),cr,CHAR(13),txt,SUBSTITUTE(B3,lf,lf&cr),SUBSTITUTE(SUBSTITUTE("<Action type=""2"" message=""0"" wParam=""42024"" lParam=""0"" sParam="""&MID(txt,SEQUENCE(LEN(txt)),1)&""" />",lf,"&#x000D;"),cr,"&#x000A"))
Dynamic array formulas.



If you do want to keep the intermediate columns then try this in column C

reaktorblue.xlsm
BC
3This is a sample This is a sample
4
5
6T
7h
8i
9s
10
11i
12s
13
14a
15
16s
17a
18m
19p
20l
21e
22&#x000D;
23&#x000A
24&#x000D;
25&#x000A
26T
27h
28i
29s
30
31i
32s
33
34a
35
36s
37a
38m
39p
40l
41e
42
Sheet2
Cell Formulas
RangeFormula
C6:C41C6=LET(lf,CHAR(10),cr,CHAR(13),txt,SUBSTITUTE(B3,lf,lf&cr),SUBSTITUTE(SUBSTITUTE(MID(txt,SEQUENCE(LEN(txt)),1),lf,"&#x000D;"),cr,"&#x000A"))
Dynamic array formulas.
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
This works great! would there also be a way to substitute < and > to "&lt;" and "&gt;"?

It looks like maybe CHAR(60) and CHAR(62) but I'm having trouble figuring how to add that in there also
 
Upvote 0
This works great! would there also be a way to substitute < and > to "&lt;" and "&gt;"?
Do you only mean if "<" or ">" occurs in the B3 text?

reaktorblue.xlsm
BCDE
3ab cd <ef> gh
4
5
6<Action type="2" message="0" wParam="42024" lParam="0" sParam="a" />
7<Action type="2" message="0" wParam="42024" lParam="0" sParam="b" />
8<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000D;" />
9<Action type="2" message="0" wParam="42024" lParam="0" sParam="&#x000A" />
10<Action type="2" message="0" wParam="42024" lParam="0" sParam="c" />
11<Action type="2" message="0" wParam="42024" lParam="0" sParam="d" />
12<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
13<Action type="2" message="0" wParam="42024" lParam="0" sParam="&lt;" />
14<Action type="2" message="0" wParam="42024" lParam="0" sParam="e" />
15<Action type="2" message="0" wParam="42024" lParam="0" sParam="f" />
16<Action type="2" message="0" wParam="42024" lParam="0" sParam="&gt;" />
17<Action type="2" message="0" wParam="42024" lParam="0" sParam=" " />
18<Action type="2" message="0" wParam="42024" lParam="0" sParam="g" />
19<Action type="2" message="0" wParam="42024" lParam="0" sParam="h" />
20
Sheet3
Cell Formulas
RangeFormula
E6:E19E6=LET(lf,CHAR(10),cr,CHAR(13),txt,SUBSTITUTE(B3,lf,lf&cr),"<Action type=""2"" message=""0"" wParam=""42024"" lParam=""0"" sParam="""&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(txt,SEQUENCE(LEN(txt)),1),lf,"&#x000D;"),cr,"&#x000A"),"<","&lt;"),">","&gt;")&""" />")
Dynamic array formulas.
 
Upvote 0
Thank you so much for the help! That works great! Would it be too much trouble to ask for the formula that would do the same thing if I wanted to keep the columns?

I have a few things I'd be using this for and I think that might help better for my use case while I learn how that function works

Thank you so much for helping me with this, I greatly appreciate the time you took to help!
 
Upvote 0
Would it be too much trouble to ask for the formula that would do the same thing if I wanted to keep the columns?
Sure, we just have to physically remove the prefix & suffix from the post #13 formula

reaktorblue.xlsm
BC
3This is a sample This is a sample cd <ef> gh
4
5
6T
7h
8i
9s
10
11i
12s
13
14a
15
16s
17a
18m
19p
20l
21e
22&#x000D;
23&#x000A
24&#x000D;
25&#x000A
26T
27h
28i
29s
30
31i
32s
33
34a
35
36s
37a
38m
39p
40l
41e
42&#x000D;
43&#x000A
44c
45d
46
47&lt;
48e
49f
50&gt;
51
52g
53h
Sheet2
Cell Formulas
RangeFormula
C6:C53C6=LET(lf,CHAR(10),cr,CHAR(13),txt,SUBSTITUTE(B3,lf,lf&cr),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(txt,SEQUENCE(LEN(txt)),1),lf,"&#x000D;"),cr,"&#x000A"),"<","&lt;"),">","&gt;"))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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