1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel question

Discussion in 'General Scripting Chat' started by Shibbi90, Mar 11, 2017.

  1. Shibbi90

    Shibbi90 Regular Member

    Joined:
    Mar 3, 2011
    Messages:
    387
    Likes Received:
    106
    Gender:
    Male
    Location:
    Norway
    Hi,

    I have X amount of rows with text in this format "XXXXX,XXXXX,XXXXX,XXXXX,XXXX" in one cell. All the text is in one cell. Due to software issues I can't split the text up to different cells.

    I need to replace some of the text in each "cells". All the text is different so I can't use find and replace.

    Is there any function for replacing only the text with Z and leave the other "cells"?

    Example:
    XXXX,XXXX,ZZZZ,XXXX turns into XXXX,XXXX,YYYY,XXXX?

    I could split the cells after each comma, replace the text in a row and then merg the cells again. But a fuction would save me more time :)

    Ps. Not sure what section of the forums I should pick, so mods feel free to move it
     
  2. kickthat

    kickthat Jr. VIP Jr. VIP

    Joined:
    Sep 18, 2014
    Messages:
    475
    Likes Received:
    596
    Gender:
    Male
    Location:
    UK
    If this is in csv format then open it in notepad. Find and replace commas with a tab (basically press tab in notepad and copy it to paste in the find and replace box).
    Then you should be able to either open the file in excel or copy and paste the data into excel - can't remember which but one of those works.
     
  3. Shibbi90

    Shibbi90 Regular Member

    Joined:
    Mar 3, 2011
    Messages:
    387
    Likes Received:
    106
    Gender:
    Male
    Location:
    Norway
    You can split text in excel after each comma, into different cells. I am using that method now, but if I could just replace the text in a specific part of a string that would save me a lot of time.
     
  4. kickthat

    kickthat Jr. VIP Jr. VIP

    Joined:
    Sep 18, 2014
    Messages:
    475
    Likes Received:
    596
    Gender:
    Male
    Location:
    UK
    If the text you want to replace is always the same number of characters into the string then I think you could use string replace formula along with left/right. I have done something along those lines in the past. Though if the length of the values between the commas differs in each cell that won't help you...
    That's the best I can offer off the top of my head
     
  5. Shibbi90

    Shibbi90 Regular Member

    Joined:
    Mar 3, 2011
    Messages:
    387
    Likes Received:
    106
    Gender:
    Male
    Location:
    Norway
    It's different length of text. No problem tho, thanks for trying :)
     
  6. mnunes532

    mnunes532 Supreme Member

    Joined:
    Jan 21, 2014
    Messages:
    1,439
    Likes Received:
    468
    Gender:
    Male
    Location:
    Portugal
    • Thanks Thanks x 1
  7. Shibbi90

    Shibbi90 Regular Member

    Joined:
    Mar 3, 2011
    Messages:
    387
    Likes Received:
    106
    Gender:
    Male
    Location:
    Norway
    Thanks, I will check it out :)