+ Reply to Thread
Results 1 to 5 of 5

Replace values in a column with new string

  1. 08-31-2017, 06:52 AM #1
    Mihaita
    Mihaita is offline
    Registered User
    Join Date
    08-31-2017
    Location
    Cluj-Napoca
    MS-Off Ver
    Office 365
    Posts
    5

    Replace values in a column with new string

    Hello,

    I would like to as you if there is a way to replace the values in a column with a new string, like in the example bellow. Creating a new column with the replaced string will also work.

    Old column
    Project no.

    044925
    044926
    047005
    047095
    047096
    047098

    New column
    Project no.

    (UPPER(WB_OFFNR)=UPPER('044925'))
    (UPPER(WB_OFFNR)=UPPER('044926'))
    (UPPER(WB_OFFNR)=UPPER('047005'))
    (UPPER(WB_OFFNR)=UPPER('047095'))
    (UPPER(WB_OFFNR)=UPPER('047096'))
    (UPPER(WB_OFFNR)=UPPER('047098'))

    I need this new string to use in the ERP i'm working with.
    Thanks!
    Reply With QuoteRegister To Reply
  2. 08-31-2017, 07:01 AM #2
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Replace values in a column with new string

    Welcome to the forum.

    If your original numbers start in A2, put this in B2 and drag down:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    (watch out for the double-quotation marks with single-quotation marks)
    Once they're all done, copy them all and then use 'Paste Values' to overwrite the formula with the results - you can then delete the original column.

    Hope that does what you want.

    Edit: as your values have leading zeroes, you might find that they drop off with the formula above, depending on how your original numbers are stored/formatted. If that happens, use this formula instead:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by Aardigspook; 08-31-2017 at 07:03 AM.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  3. 08-31-2017, 08:12 AM #3
    Mihaita
    Mihaita is offline
    Registered User
    Join Date
    08-31-2017
    Location
    Cluj-Napoca
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Replace values in a column with new string

    Hello,

    Thanks for the welcoming and also for the formula!
    It works like a charm!

    Have a good day!
    Reply With QuoteRegister To Reply
  4. 08-31-2017, 02:46 PM #4
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Replace values in a column with new string

    Glad I could help.

    As you're new, you may not know that you can mark the thread as Solved so others know there's an answer here. Instructions to do it are in my sig - if you could take a moment to do it, that would be appreciated. Thanks.
    Reply With QuoteRegister To Reply
  5. 09-01-2017, 01:03 AM #5
    Mihaita
    Mihaita is offline
    Registered User
    Join Date
    08-31-2017
    Location
    Cluj-Napoca
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Replace values in a column with new string

    Done! Thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Replace different part of string with different values
    By vistrup in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2016, 03:41 AM
  2. [SOLVED] Replace cell in Column A with value in Column B if contains similar character string
    By oybombadil in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 08:49 AM
  3. [SOLVED] Find and Replace a String of Values in a Given Row
    By glennchung in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-15-2014, 12:03 PM
  4. Replace Items in a string with corresponding values from an array
    By william1404 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-25-2014, 09:22 PM
  5. Macro to identify column by header and replace number values with string values
    By kang6789 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2013, 05:44 PM
  6. Unique # in one column that needs to replace X in string of text in another column
    By dquinte1 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 08-29-2010, 03:31 PM
  7. Dynamic Find and Replace String Within Functions Based on Column Values
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2005, 02:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1