locked
How to truncate column of cells with different values? RRS feed

  • Question

  • I have a column of cells that have alphanumeric computer names, the computer names are different lengths.  I would like to truncate the first four characters AND the last two characters and put the new values in a different column.

    For example:

    A computer name CHWIUSERID01 should be truncated to USERID

    A computer named CHWIUSR01 should be truncated to USR

    Essentially I want to chop-off the first four and last two characters of each value.  

    I can use TEXT TO COLUMNS to truncate the first four, but since the value lengths are different I cannot use TEXT TO COLUMNS to truncate the last two.  Is there a better way to accomplish this?

    Thanks,

    FP

    Thursday, July 23, 2020 3:46 PM

All replies

  • To:  FP
    re:  extracting text

    Data starting in B3 ...
      =MID(LEFT(B3,LEN(B3)-2),5,999)
    '---



    Free Excel programs at MediaFire...
    The Custom_Functions add-in has 20+ new Excel functions including the X_vLookUp function.
    Download (no ads) from...  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Thursday, July 23, 2020 5:26 PM
  • Thanks!!!

    FP

    Thursday, July 23, 2020 5:51 PM
  • Hi FP,

    If your problem is solved, please remember to mark the helpful reply as an answer. Your action would be helpful to other users who encounter the same issue and read this thread.

    Thanks for your understanding.

    -----------------------------------------------------------

    This “Excel IT Pro Discussions” Forum will be migrating to a new home on Microsoft Q&A, please refer to this sticky post for more details.

    Regards,

    Emily


    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !
    Friday, July 24, 2020 3:04 AM
  • Hi FP,

    Please do not forget to mark helpful reply as an answer or please kindly share us your solution and mark your reply as an answer.

    Any questions you may post back.

    Regards,

    Emily


    "Office" forums will be migrating to a new home on Microsoft Q&A !
    We invite you to post new questions in the "Office" forums' new home on Microsoft Q&A !
    Monday, July 27, 2020 8:49 AM