+ Reply to Thread
Results 1 to 12 of 12

Remove characters from cells if the have non data and characters as per attached sheet

  1. #1
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Remove characters from cells if the have non data and characters as per attached sheet

    Greetings

    I have a sheet which contains racedata that has been scrapped for me.

    I am able to copy the information from the racadata worksheet into different sheets within my workbook

    I am experiencing a problem in certain events where the number of runners is than that the spaces on the template

    How can I write a formula or macro that will clear the ### character and or the zeros when the the data in column C is blank or empty. It must however keep the formula because in future events the column C can be filled in with names

    Thanx for all help received


    REagrds

    rajann
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    You've not included a Formula on the Worksheet, but it might be possible to just wrap it in IFERROR to stop the N/A

    =IFERROR([Your formula],"")

    Does that work for you?

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    May be this
    In Q2 then copied down

    =IF(C2="","", YourFormula)
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Hi

    Thank you for responding

    I did not realise that my attachment had no formula

    I am uploading the racedata file with formula

    Thanx once again for the help

    REgards

    raj
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Hi

    Thanx for the help

    I attached the updated sheet with formula

    REgards

    Rajann

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Which formula you are referring.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    As mentioned in post#2 simply wrap the formula in IfError like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Hi

    Thank you so much

    Its working great

    May I ask a further question

    Kind regards

    Rajann

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    You're welcome & thanks for the feedback

  10. #10
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Hi

    Thank you so much for the solution. Its working, I made a few changes to my data and it worked

    I have a question

    Currently I am cutting and pasting data from one file and pasting it into the template

    Can this process be automated every time I get a new file

    The template has fixed spaces to which I paste the data. The identifiers in both files will be race and number

    Thank you in anticipation

    rajann
    Last edited by khanaran; 05-17-2019 at 12:20 AM. Reason: change my question

  11. #11
    Valued Forum Contributor PaulSP8's Avatar
    Join Date
    11-21-2017
    Location
    Chorley, England
    MS-Off Ver
    2013 & 2021
    Posts
    437

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    You probably can, but that's a completely different thing to this.

    Open another question and post examples of both your original sheet and the template sheet with what you'd like to happen.

  12. #12
    Forum Contributor
    Join Date
    09-25-2011
    Location
    Lenasia, Johannesburg,South Africa
    MS-Off Ver
    Excel 2019
    Posts
    269

    Re: Remove characters from cells if the have non data and characters as per attached sheet

    Hi

    Thank you for the response

    I will be doing so

    REgards

    Rajann

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Remove characters from string from the right after 4 characters
    By dlemley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-10-2017, 08:47 AM
  2. Remove Foreign Characters From CSV Sheet
    By natebloom in forum Excel General
    Replies: 1
    Last Post: 01-23-2017, 05:36 PM
  3. Remove Text characters, leaving numerical characters
    By aschwalge in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-01-2016, 06:42 PM
  4. [SOLVED] Remove only characters from cells from last
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2015, 10:08 AM
  5. Remove Characters from Cells
    By shaibn in forum Excel General
    Replies: 3
    Last Post: 09-17-2015, 11:07 AM
  6. [SOLVED] VBA, remove all characters within a string, except numerical characters and full stops
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 09:24 PM
  7. Remove last 15 characters from all cells in a row...
    By DaddyChan in forum Excel Formulas & Functions
    Replies: 29
    Last Post: 04-15-2013, 12:21 PM

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