topic Re: Parse String with no Delimiter in Discussions

Parse String with no Delimiter

PopulationWolf4 — Tue, 18 Jul 2023 18:17:40 GMT

Hi,

I have 3 columns: A, B, and C. Each column contains a string like "A121A5*6". I would like to parse each alphabet or the * symbol and subsequent number into separate columns. With the example string, there will be 3 columns with A121, A5, and *6. I want to do that for Columan A, B, and C, except when the value of the column is NA. How do I do this? Thank you!

Re: Parse String with no Delimiter

jthi — Tue, 18 Jul 2023 18:31:41 GMT

Can there be more than three columns to be created from single column? Are the alphabet known which will be used for splitting? Are they always same in a same cell? Can there be multiple split characters in row and if there can how should those situations be handled?

Re: Parse String with no Delimiter

PopulationWolf4 — Sat, 22 Jul 2023 22:19:57 GMT

Yes, there can be more than 3 columns created from a single column. If a single column has A1B12C34D112, 3 columns would be needed for A1, B12, C34, and D112.

Yes, the alphabets are known.

what do you mean by are they always in the same cell?

Yes, there can be multiple split characters in a row, in that case, more columns will need to be created to accomandate.

thanks!

Re: Parse String with no Delimiter

jthi — Sun, 23 Jul 2023 13:39:53 GMT

Could you provide example data table with as many of different types of cases as possible (different amounts of alphabets, different alphabets, with *, without *, alphabets in row and so on) and then solutions to them. If the alphabets are known how are they known? By row?

By "are they always in same cell" I think I did mean if there is always same alphabet in a specific cell but based on your response there can be many different alphabets in single cell.

Currently only fairly easy solution that comes to my mind is to use For Each Row and go row by row while creating new columns as needed.