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

String Split to Columns

  1. #16
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    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
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    Re: String Split to Columns

    johnmpl, your file works very well, and is simple

  3. #18
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    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
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    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
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    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
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    Re: String Split to Columns

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

  7. #22
    Registered User
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365 with 2016
    Posts
    74

    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
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    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
    Registered User
    Join Date
    05-20-2016
    Location
    Detroit
    MS-Off Ver
    2016
    Posts
    87

    Re: String Split to Columns

    01. If CELL Table 1 Column A =

+ 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