String Text to Columns

[ionelz]

[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]

 

[Rick Rothstein]

I did a Copy /Paste

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

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

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)?

 

[ionelz]

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

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)?

 

[ionelz]

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

 

[Rick Rothstein]

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

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.

 

[ionelz]

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 (

 

[ionelz]

Ok I want to make it simple, just split the Column A

I have removed the condition with text in () String	A	B	C	D	G	K
A10 B20 G300 A103 K35 A48 A476 D12 A66 A76	10,103,48,476,66,76	20			300	35
A49 C100 G2	49		100		2
A10 C20 K21	10		20			21
A40 B22 D21 A33	40,33	22		21
Split Numbers after character in Column String to "coresponded" Columns as shown
A - have Multiple Repetitions (6 MAX) , the rest B,C,D,E,F ar unique per String

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Ok I want to make it simple, just split the Column A

I have removed the condition with text in ()

<tbody>
</tbody>

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.

 

[hiker95]

Re: String Text to Columns
Could you tell me how to attach an EXCEL file so I can better describe my question

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

 

[Ingolf]

Cross post https://www.excelforum.com/excel-general/1215341-string-split-to-columns.html

You have lot's of answers there

 

[hiker95]

ionelz,

A message to forum cross posters.

Please read the following:

http://www.excelguru.ca/node/7