Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Remove first character of cell

  1. #1
    Join Date
    21st September 2004
    Posts
    1

    Remove first character of cell

    how do you remove the first character inside a cell?

    -its always the first
    -its always the same character, "|"

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th June 2004
    Location
    UK
    Posts
    134
    Hi,

    Assuming data in cell A1 ....then in B1 type in =MID(A1,2,LEN(A1))

    HTH

    Cheers
    Stu

  3. #3
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676
    or

    =REPLACE(A1,1,1,"")

    or

    =RIGHT(A1,LEN(A1)-1)

    where A1 is the cell with your errant value

    Take your pick
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    Especially if the "|" occurs only that one time in each cell, you can use (for cell A2):

    =SUBSTITUTE(A2,CHAR(124),"")

    where 124 is the code for "|" (I think). You can double check by using:

    =CODE(LEFT(A2,1))
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  5. #5
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676
    Heheee

    Love this place ... what a diverse set of solutions (& tom... that does work )
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  6. #6
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    You are absolutely correct, (As Always), Will. And you stimulated me to yet one more variation.

    =TRIM(SUBSTITUTE(A2,CHAR(124),""))

    This will also remove leading and trailing spaces (even "leading" spaces that follow the "|"). -- e.g. if teh cell contained "| text" or "| text "
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Remove All Data After Certain Character
    By catch84 in forum EXCEL HELP
    Replies: 4
    Last Post: February 26th, 2008, 11:36
  2. Remove Last Character From Cell
    By sriya in forum EXCEL HELP
    Replies: 2
    Last Post: January 8th, 2007, 13:25
  3. Remove Last Character From Cell Text
    By Red_Adam in forum EXCEL HELP
    Replies: 2
    Last Post: September 14th, 2006, 23:37
  4. Remove Character From Text
    By spikel in forum EXCEL HELP
    Replies: 2
    Last Post: June 18th, 2004, 20:21
  5. [Solved] VBA : Remove first character
    By L.B. in forum EXCEL HELP
    Replies: 2
    Last Post: January 6th, 2004, 00:12

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
  •  
porno