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)
 
Sort by date Sort by votes
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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
reaktorblue said:
This works great! would there also be a way to substitute < and > to "&lt;" and "&gt;"?
Click to expand...
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
reaktorblue said:
Would it be too much trouble to ask for the formula that would do the same thing if I wanted to keep the columns?
Click to expand...
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
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,051
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