String Text to Columns

I

ionelz

Active Member
Joined
Jan 14, 2018
Messages
254
Office Version
  1. 365
Platform
  1. Windows
[FONT=&quot]In my TABLE, Column1 have this type of Strings: A10 B15 A21 G33 B121 A1000 (THIS IS TEXT)[/FONT][FONT=&quot][/FONT][FONT=&quot]
I would like to SPLIT these Strings to Corresponded Columns, so 10 goes to Column A, 15 to Column B, 21 to Column A, 33 to Column G, 121 to Column B, 1000 to Column A, THIS IS A TEXT to Column2. So in Column A is 10,21,1000 in Column B is 15,121 in Column G is 33 and in Column2 THIS IS A TEXT. Text in () should always go to Column1[/FONT]
 
Sort by date Sort by votes
ionelz said:
I did a Copy /Paste


[TABLE="width: 860"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]String[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]A10 B20 G300 A103 K35 A48 A476 D12 A66 A76 (THIS IS AN TEXT)[/TD]
[TD]10,103,48,476,66,76[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]A49 C100 G2[/TD]
[TD]49[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A10 C20 K21[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A40 B22 D21 A33[/TD]
[TD]40,33[/TD]
[TD]22[/TD]
[TD] [/TD]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]Split Numbers after character in Column String to "coresponded" Columns as shown[/TD]
[/TR]
[TR]
[TD="colspan: 7"]A - have Multiple Repetitions (6 MAX) , the rest B,C,D,E,F ar unique per String[/TD]
[/TR]
[TR]
[TD="colspan: 7"]If possible the SPLIT should stop before character ( so look only in the left side of character ([/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Is what you show in the "String" column actually values in Column A to start and, after processing, did you want the values you show under the letter A to replace the string so the "A" values are all in Column A or do you want to keep the original string values and have the split out "A" values go to Column B (which I would guess has the letter "A" as its header text in cell B1)?
 
Upvote 0

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)
YES.
In Column A (String), I show the "request" : split that string to columns
In Column B,C,D .... I show what I want, the result of split
So, I want to Keep Column A as is, and to the split

Rick Rothstein said:
Is what you show in the "String" column actually values in Column A to start and, after processing, did you want the values you show under the letter A to replace the string so the "A" values are all in Column A or do you want to keep the original string values and have the split out "A" values go to Column B (which I would guess has the letter "A" as its header text in cell B1)?
Click to expand...
 
Upvote 0
So, I want to Keep Column A as is, and to do the split.
I do not want to look in after character ( if this is possible, because if in that text may exist (I want D20mm bigger) then 20 will go in Column D

 
Upvote 0
ionelz said:
I do not want to look in after character ( if this is possible, because if in that text may exist (I want D20mm bigger) then 20 will go in Column D
Click to expand...
What does what I highlighted in red mean? What opening parenthesis are you talking about?

What about what I highlighted in blue... does that mean your values do not always start with a single letter followed by a digit?

I think it would be helpful if you posted a representative sample of the type of values that can be in your "String" column.... and make sure you show us any variations that exist so we can know what you know about your data.
 
Upvote 0
Ok, please see Column A : A10 B20 G300 A103 K35 A48 A476 D12 A66 A76 (THIS IS AN TEXT ...D20aaa)
At the end is an text in parenthesis ()
So I want the SPLIT Formula not to look after at the text in parenthesis.
That is the meaning above of : not looking after (
 
Upvote 0
Ok I want to make it simple, just split the Column A
[TABLE="width: 860"]
<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]I have removed the condition with text in ()
String

[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]A10 B20 G300 A103 K35 A48 A476 D12 A66 A76[/TD]
[TD]10,103,48,476,66,76[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]300[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]A49 C100 G2[/TD]
[TD]49[/TD]
[TD] [/TD]
[TD]100[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A10 C20 K21[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]A40 B22 D21 A33[/TD]
[TD]40,33[/TD]
[TD]22[/TD]
[TD] [/TD]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="colspan: 7"]Split Numbers after character in Column String to "coresponded" Columns as shown[/TD]
[/TR]
[TR]
[TD="colspan: 7"]A - have Multiple Repetitions (6 MAX) , the rest B,C,D,E,F ar unique per String[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
ionelz said:
Ok I want to make it simple, just split the Column A
[TABLE="width: 860"]
<tbody>[TR]
[TD]I have removed the condition with text in ()[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Well, that may make the question easier for now, but if you plan on coming back later and asking us to incorporate whatever it is you have in mind for text in (), that will more than likely require a complete rewrite of any code we come up with for your reduced question. I am not sure about other volunteers here, but I would rather not waste my time writing code that I expect to have to throw away and to have to rewrite later. If you could just clarify what possible text values could exist in your "String" column and show us what you would want for output from them, I am sure we can give you a solution to your complete problem. The one thing I do not think you fully appreciate about the volunteers here... we know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own.
 
Upvote 0
Re: String Text to Columns
Could you tell me how to attach an EXCEL file so I can better describe my question
Click to expand...

ionelz,

1. Can we see your actual raw data workbook/worksheet?

2. And, on another worksheet, what the results (manually formatted by you) should look like?


You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
569
Domenic
D
J
  • Solved
LET/VSTACK Help
Replies
2
Views
159
Jedi Master
J
D
  • Solved
How do I generate random numbers for duplicate entries in Column A
Replies
8
Views
530
Eric W
Eric W
G
  • Solved
Compare 2 columns and find duplicates then copy corresponding cell into another cell
Replies
8
Views
747
kevin9999
K
S
  • Question Question
Replace different text/strings in multiple files in one folder
Replies
1
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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