+ Reply to Thread
Results 1 to 21 of 21

Removing a digit or character from a cell

  1. #1
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Removing a digit or character from a cell

    As I learned here

    =TEXT('Export 1'!A2,"00") & TEXT('Export 1'!B2,"0000") & TEXT('Export 1'!D2,"0000")

    allowed me to combine three cells into one cell. The input is 01 1000 1234 output is 0110001234

    The second set of number "1000" is going need to be changed to a 3 digit number. All of the numbers that are being converted have a "0" in the second position. I need a way to get rid of that zero and turn 1000 into 100 and number like 2024 to 224.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    Would this work for you?

    =TEXT('Export 1'!A2,"00") & TEXT(LEFT(B2)&MID(B2,3,2),"000") & TEXT('Export 1'!D2,"0000")

    or, what about this...

    =A2&LEFT(B2)&MID(B2,3,2)&D2

    Without more examples, it's hard to say if the second is what you need.
    Last edited by jeffreybrown; 10-22-2018 at 06:19 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    The first example doesn't look like it is exporting any data from "Export 1" for the second position.

    The second isn't exporting any data is it?

  4. #4
    Forum Contributor
    Join Date
    04-30-2015
    Location
    The Netherlands
    MS-Off Ver
    office 365
    Posts
    195

    Re: Removing a digit or character from a cell

    Just amending Jeffrey's formula's

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    He forgot to add the reference to the other sheet (The "'Export 1'!" part)
    Remember to mark as Solved and give out rep.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    Thanks FerdyHar for the catch.

    On the second formula, I also forgot the sheet name...

    ='Export 1'!A2&LEFT('Export 1'!B2)&MID('Export 1'!B2,3,2)&'Export 1'!D2
    Last edited by jeffreybrown; 10-23-2018 at 09:27 AM.

  6. #6
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    Using the first Formula turned this...

    01 1000 1319
    01 1000 1334
    01 1000 0464
    01 1000 2051
    01 1000 2404
    01 1000 2602
    01 1000 0574
    01 1000 1381
    01 1000 0227
    01 1000 0211
    01 1000 1732
    01 1000 2706
    01 1000 0623
    01 1000 0943
    01 1000 0967
    01 1000 2087
    01 1000 0737

    Into this...


    010011319
    010011334
    01RRe0464
    012051
    012404
    012602
    010574
    011381
    010227
    010211
    011732
    012706
    010623
    010943
    010967
    012087
    010737

    The second formula seems to have worked except for the losing the 0 place holder when the 3rd group only has 3 digits. I've manually added the 0 in red. It is also missing the leading 0.

    11001319
    11001334
    11000464
    11002051
    11002404
    11002602
    11000574
    11001381
    11000227
    11000211
    11001732
    11002706
    11000623
    11000943
    11000967
    11002087
    11000737

  7. #7
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    I just thought. Would it be better (easier) to convert the numbers on the original sheet before they are exported?

    If so what would be the formula to (on the same sheet) remove the second position 0?

  8. #8
    Forum Contributor
    Join Date
    04-30-2015
    Location
    The Netherlands
    MS-Off Ver
    office 365
    Posts
    195

    Re: Removing a digit or character from a cell

    Try this formula instead of Jeffrey's second formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This should take out the second place 0 but if there is only 3 characters it should still get them all

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    Can you attach a sample workbook with what you have and what you are using? Need to see the results you get and the results you desire.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  10. #10
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    I will attach a sample. I'm going to have to clean one up because what I have now has SSNs and wages.

  11. #11
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    That was easier than I thought it would be to clean up but the forum isn't allowing me to attach the spreadsheet.

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    Is the forum not allowing you to upload a spreadsheet or are you being blocked from the location you are trying to upload from. I don't seem to have a problem using the method in post #9.

    In post #6, I get the third results, so don't know why you are getting the results in the middle.

  13. #13
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    It is the forum. I upload things to other forums (including ones using the same forum software) all the time.

    All I get when I click the Attachment icon is what looks like an empty drop down menu.

    I even tried to PM it to you but there is no attachment icon in the PM function I could find.

    I wonder if there is a minimum number of posts before you can upload. Maybe a senior member like yourself could get the mods to give me access.


    I found the Attachments works.
    Attached Files Attached Files
    Last edited by GigG; 10-23-2018 at 11:19 AM.

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    If there is, it's 10 posts, but now that I think about it, I've seen attachments by members with 1 post.

  15. #15
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    Yep, it is allowing it now. I attached it to my last post.

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    For...

    01 1000 0623

    ...what result do you expect?

    01100623

  17. #17
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    No,

    011000623

    What I'm needing is

    Two digits in with the first export (01)

    Three digits in the second export 100. In all cases, the original 4 digit number has a 0 in the second position and I want that removed.)

    Four digits in the third export (1234 or with a zero added to the beginning if the original number is a 3 digit number)



    Like I mentioned earlier If it would be easier to make the number transitions, especially on the middle number before the export I'm good with that.

  18. #18
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    This produces the results you describe above.

    =TEXT('Export 1'!A14,"00") & TEXT(LEFT(B14)&MID(B14,3,2),"000") & TEXT('Export 1'!D14,"0000")

    If this is not the case, in your attachment, please update to show the proper results next to the results from the formula.

  19. #19
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    That formula works fine in the first 2 rows and then fails with this result.

    010011319
    010011334
    01RRe0464
    012051
    012404
    012602
    010574
    011381
    010227
    010211
    011732
    012706
    010623


    I've attached the spreadsheet with that formula in it.
    For the record, I'm entering the formula in cell D5, correcting it to export from cells A2, B2, and D2 and then filling down.
    Attached Files Attached Files

  20. #20
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Removing a digit or character from a cell

    I'm really trying hard to understand, but this seems to do what you ask...

    =TEXT('Export 1'!A2,"00") & TEXT(LEFT('Export 1'!B2)&MID('Export 1'!B2,3,2),"000") & TEXT('Export 1'!D2,"0000")

    Data Range
    D
    5
    011001319
    6
    011001334
    7
    011000464
    8
    011002051
    9
    011002404
    10
    011002602
    11
    011000574
    12
    011001381
    13
    011000227
    14
    011000211
    15
    011001732
    16
    011002706
    17
    011000623

  21. #21
    Registered User
    Join Date
    10-19-2018
    Location
    Arkansas
    MS-Off Ver
    MS Excel for Mac 365
    Posts
    33

    Re: Removing a digit or character from a cell

    It does. The formula in Post #18 was missing the Export 1 call in the second group.

    =TEXT('Export 1'!A14,"00") & TEXT(LEFT(B14)&MID(B14,3,2),"000") & TEXT('Export 1'!D14,"0000") as opposed to

    =TEXT('Export 1'!A2,"00") & TEXT(LEFT('Export 1'!B2)&MID('Export 1'!B2,3,2),"000") & TEXT('Export 1'!D2,"0000")


    Thanks for you help.

+ 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] LOTTERY FILTER#4, Find if 1 Digit Sum of 2 Digit or 3 Digit, Single Cell w/ dash
    By david gonzalez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2014, 12:57 AM
  2. [SOLVED] MACRO: removing everything from cell after 10th character
    By pRobson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2014, 07:35 AM
  3. Removing text based on a character in a cell
    By Nate L in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-30-2013, 09:35 AM
  4. removing a character from a cell
    By wstarkey in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-15-2012, 08:07 AM
  5. Regarding character removing in cell
    By ramki in forum Excel General
    Replies: 3
    Last Post: 06-13-2008, 12:50 AM
  6. how to remove all the spaces behind the last character/digit
    By ttik in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2007, 11:07 PM
  7. [SOLVED] Removing last character of cell
    By mira in forum Excel General
    Replies: 4
    Last Post: 07-27-2005, 03:05 PM
  8. Replies: 2
    Last Post: 02-07-2005, 05:12 AM

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