+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 27 of 27

String Split to Columns

  1. #16
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

    Re: String Split to Columns

    Hi, to all!

    If you have this cover:
    Quote Originally Posted by Zer0Cool View Post
    • The repeated alpha sequences are consecutive within the string (IE: "A1 A2 A3 B1 C1", NOT "A1 B1 A2 C1 A3")
    You can use this formula:
    [B2] : =IFERROR(SUBSTITUTE(SUBSTITUTE(MID($A2,FIND(B$1,$A2),FIND(" ",$A2&" ",FIND("|",SUBSTITUTE($A2,B$1,"|",LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")))))-FIND(B$1,$A2)),B$1,"")," ",", "),"")

    Check file. Blessings!
    Attached Files Attached Files

  2. #17
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    johnmpl, your file works very well, and is simple

  3. #18
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    I can not count the cells with text only.
    For example, in ExtractNumbers.xlsx (attached file above) if a want to count cells with text/value in column using count(column_range,"*") still count all the cells

  4. #19
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

    Re: String Split to Columns

    Quote Originally Posted by ionelz View Post
    if a want to count cells with text/value in column using count(column_range,"*") still count all the cells
    Use:
    =COUNTIF(B2:G2,"?*")
    or
    =COUNTIF(B2:G2,"><0")

    Blessings!

  5. #20
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    This is crazy question
    In the file : ExtractNumbers.xlsx (attached file above) is anyway to UPDATE back the STRING if any modification in columns ?
    So for example in cell A2B2 if I change from 1 to 6 can I get in an updated String to A6 C2 F5 ?

  6. #21
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    or Create another Column, String1 and concatenate back the columns A ..F to String1 column

  7. #22
    Forum Contributor
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    176

    Re: String Split to Columns

    Quote Originally Posted by ionelz View Post
    This is crazy question
    In the file : ExtractNumbers.xlsx (attached file above) is anyway to UPDATE back the STRING if any modification in columns ?
    So for example in cell A2B2 if I change from 1 to 6 can I get in an updated String to A6 C2 F5 ?
    This is possible with VBA, because in a cell you can have a value or a formula, not both.

    Quote Originally Posted by ionelz View Post
    or Create another Column, String1 and concatenate back the columns A ..F to String1 column
    Try, for example:
    =TRIM(IF(B2="","",SUBSTITUTE(", "&B2,", "," "&B$1))&IF(C2="","",SUBSTITUTE(", "&C2,", "," "&C$1))&IF(D2="","",SUBSTITUTE(", "&D2,", "," "&D$1))&IF(E2="","",SUBSTITUTE(", "&E2,", "," "&E$1))&IF(F2="","",SUBSTITUTE(", "&F2,", "," "&F$1))&IF(G2="","",SUBSTITUTE(", "&G2,", "," "&G$1)))

    Blessings!
    Last edited by johnmpl; 01-12-2018 at 11:01 PM.

  8. #23
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    I have added a new Table 2 to the File, see attached.
    I would like to COMPARE Column A from Table 1 with Column A from Table 2 and put the result in Table 1 Column A Meaning
    01. If Table CELL in 1 Column A = CELL Table 2 Column A say A Meaning from Table 2 otherwise say "Invalid"
    02. If in Table 1 Column A is a "String" (like see in Table 1 "4,7,5") , here without comparation simple say "Text 2"
    Thanks
    Attached Files Attached Files

  9. #24
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    01. If CELL Table 1 Column A =

  10. #25
    Forum Contributor
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    146

    Re: String Split to Columns

    Could you PLEASE update the Formula you wrote in attached file, if possible ?
    You wrote the Formula for Maximum 5 Repetition and Work Great.
    But now I know that, ONLY ONE in String can have Multiple repetitions (named always G) the rest are unique.
    Will this make the Formula simple ?
    Also, I have added Cell A9
    Can the Formula look in String until Character * ? (so ONLY in the LEFT side of * and Ignore the String in the Right side of star)
    Thank You


    Quote Originally Posted by Glenn Kennedy View Post
    I have no idea what this means "Also, because probably of "" (at the end of formula) or I am not sure, when I count the the column cells, it count all lines even there is no data in cells
    I use countif(range,"")"

    try this.
    Attached Files Attached Files

  11. #26
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: String Split to Columns


  12. #27
    Valued Forum Contributor
    Join Date
    09-10-2017
    Location
    Chippenham
    MS-Off Ver
    2013
    Posts
    837

    Re: String Split to Columns

    Already mentioned

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1