+ Reply to Thread
Results 1 to 16 of 16

Problem with delimited data in worksheet

  1. #1
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Problem with delimited data in worksheet

    Good Afternoon

    I have a worksheet that has around 2000 rows each containing data as shown in the attached sample sheet. I want to replace all the squares (is this some form of delimiting or seperating character inserted as the data was imported?) with a space and leave the other data intact.

    I've tried find & replace to do this without success, although editing each individual cell works. Please let me know as I need a way to do this for all cells at once as it would take an age to do each cell indivudually.

    Hope you can assist.

    Thanks

    VC
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-27-2009
    Location
    Omaha, Nebraska
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Problem with delimited data in worksheet

    Your attached file is essentially blank. Is that the correct file?

  3. #3
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    Might be me -- attachments gone !!!!

  4. #4
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Re: Problem with delimited data in worksheet

    Thank you for your reponse.

    Please see attached, it opened OK for me.

    Thanks

    VC
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-27-2009
    Location
    Omaha, Nebraska
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Problem with delimited data in worksheet

    All I see in that attachement is some text in cell A1. Everything else is blank! It's probably something on my end.

  6. #6
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Re: Problem with delimited data in worksheet

    OK you are seeing the file. Can you see some small squares within the text?

    Please let me know what you see.

    It's these that I want to remove to be replaced with spaces.

    Cheers, thanks for bearing with me.

    VC

  7. #7
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    A1 one only here says:

    HEAVY MALE 90 DEGREE BEND : BLACK TUBULAR SCREWED BSP TO

    BS 1387 TABLE 10 FIGURE 6 TYPE 1 COMPLETE WITH EQUAL SOCKET

    BLACK WROUGHT IRON SCREWED BSP TO BS 1740 TABLE 9 FIGURE 15

    SIZE

    3"

    Note the squares are missing in my post BUT ARE in yours

  8. #8
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Re: Problem with delimited data in worksheet

    OK Thanks

    I have many cells that contain these squares and I need to remove them all and replace them with spaces, possibly using the substitute function. Please let me know the correct syntax as my attempts with this and also trying find/replace haven't worked.

    Thanks again.

    VC

  9. #9
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    =SUBSTITUTE(CELL,CHAR(NUMBER),"")

    Repalce the CELL with the traget cell ie A1 or whatever
    Replace the NUMBER with teh Char Number you want to remove

    Once edited you can drag it down
    Copy
    Paste as value
    Delete original Column

  10. #10
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Re: Problem with delimited data in worksheet

    Thanks

    Would you know the correct character number for this square. I've tried ASCII 28, 29, 30 & 31 but none of these work.

    Cheers

    VC

  11. #11
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    run this against the target ASCII

    =CODE(CELL)

    This will give it to you to use in the otehr function

  12. #12
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    Something like

    =CODE(RIGHT(CELLS,3))

    51

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Problem with delimited data in worksheet

    clean() will do it
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  14. #14
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    Quote Originally Posted by martindwilson View Post
    clean() will do it
    >>>> It's these that I want to remove to be replaced with spaces.

    Will clean leave spaces?

  15. #15
    Valued Forum Contributor
    Join Date
    07-21-2008
    Location
    London, UK
    Posts
    326

    Re: Problem with delimited data in worksheet

    Quote Originally Posted by martindwilson View Post
    clean() will do it
    Just tested as :

    HEAVY MALE 90 DEGREE BEND : BLACK TUBULAR SCREWED BSP TOBS 1387 TABLE 10 FIGURE 6 TYPE 1 COMPLETE WITH EQUAL SOCKETBLACK WROUGHT IRON SCREWED BSP TO BS 1740 TABLE 9 FIGURE 15SIZE3"

    As you see at the end 15SIZE3"

    Thats not really readable or what the original poster asked for... good call thou

  16. #16
    Registered User
    Join Date
    05-12-2004
    Posts
    27

    Re: Problem with delimited data in worksheet

    Yeh I tried clean() earlier and it gets rid of the unwanted character but leaves no spaces.

    Thanks All

    VC

+ Reply to Thread

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