1

I have to download media file databases on a daily basis and modify metadata from a csv file so that the unique numerical prefix in column B is added as a suffix to the cells in column C.

Is there a way i can do this in one go for all cells that do not yet contain the suffix in the cell names of column C?

enter image description here

At the moment, the only way i'm able to do it is to add _01 to the end of the first cell (starting _01) and drag the cell down so it auto-fills the rest of the numbers in ascending order (as seen in the screenshot below).

enter image description here

I've tried opening the file in Notepad++ to see if there's a way to do it there but unfortunately it doesn't bring in the data with columns and instead shows all the text separated by comma's in a typical notepad style. I need to maintain the file in .csv format so i can re-import the modified data onto our media server.

Does anyone know of a way that will take XX_ from column B and add it as _XX to the cells that do not have it in column C? I'm open to trying this out in any software other than Excel. I have used notepad++ for this sort of thing in the past but never with csv files and i'm not sure if the document will come out correctly formatted afterwards.

Column B:

16_9S0816YE556_GOPR0091_0S01
17_9S0816YE556_GOPR0091_1S01
18_9S0816YE556_GOPR0091_2S01
19_9S0816YE556_GOPR0091_3S01
01_9S0803SD006MN_C0001S01
02_9S0803SD006MN_C0002S01
03_9S0813ED095MN_8R7A3425S01
04_9S0813SD007MN_C0001S01

Column C:

9S0815GP174_16
9S0815GP174_17
9S0815GP174_18
9S0815GP174_19
9S0816GP175
9S0816GP175
9S0816GP175
9S0816GP175

Desired end result for Column C (Without affecting first four cells):

9S0815GP174_16
9S0815GP174_17
9S0815GP174_18
9S0815GP174_19
9S0816GP175_01
9S0816GP175_02
9S0816GP175_03
9S0816GP175_04

Thanks.

  • I'd recommend to use proper instrument. You work with an array of data, so use any database to store and process data. MS Access, for example... think about it at least. – Akina Aug 23 '18 at 9:00
  • i'm not sure if the document will come out correctly formatted afterwards. Don't think about it. Use CSV with a separator (not fixed-length) using space symbol as a separator and 'multiple separator as one' option when import back into Excel. – Akina Aug 23 '18 at 9:05
  • I'm sorry i'm not quite following what you mean here? – Myles Aug 23 '18 at 9:56
  • Assuming you only need the first two digits from B add to the end of C and you can use a helper column then try this formula: =C1&"_"&LEFT(B1;2) – Kevin Anthony Oppegaard Rose Aug 23 '18 at 9:57
  • 1
    Hi Kevin, will this affect all the cells or just the ones that do not have '_XX'. I don't want to add the numbers to the end of the cells that already have them. – Myles Aug 23 '18 at 10:22
1

With Notepad++, you can do:

  • Ctrl+H
  • Find what: ^([^,]*,(\d\d)_[^,]*,[^,]*)(?<!_\d\d),
  • Replace with: $1_$2,
  • check Wrap around
  • check Regular expression
  • Replace all

Explanation:

^           : beginning of line
(           : start group 1
  [^,]*     : 0 or more non comma character
  ,         : 1 comma
  (\d\d)    : group 2, 2 digits
  _         : underscore
  [^,]*     : 0 or more non comma character
  ,         : 1 comma
  [^,]*     : 0 or more non comma character
)           : end group 1
(?<!        : negative lookbehind, make sure we don't have before the comma
    _\d\d   : underscore and 2 digits
)           : end lookbehind
,           : a comma

Result for given example:

col_A,16_9S0816YE556_GOPR0091_0S01,9S0815GP174_16,col_D
col_A,17_9S0816YE556_GOPR0091_1S01,9S0815GP174_17,col_D
col_A,18_9S0816YE556_GOPR0091_2S01,9S0815GP174_18,col_D
col_A,19_9S0816YE556_GOPR0091_3S01,9S0815GP174_19,col_D
col_A,01_9S0803SD006MN_C0001S01,9S0816GP175_01,col_D
col_A,02_9S0803SD006MN_C0002S01,9S0816GP175_02,col_D
col_A,03_9S0813ED095MN_8R7A3425S01,9S0816GP175_03,col_D
col_A,04_9S0813SD007MN_C0001S01,9S0816GP175_04,col_D
  • Excellent! Thank you Toto i knew a notepad++ expert would fly in and save me! This worked and did exactly what i want! Again thanks! Question upvoted and answered! – Myles Aug 23 '18 at 14:24
0

If you have Excel 2016, use Microsoft's Get & Transform power tools for getting a new table with the correct column, .
(For Excel 2010-2013, use the free MS add-in Power Query for Excel.)

You can eventually connect directly to the source (csv file, database, Excel file, ...) and perform a transformation like these one (I've kept the used columns, which can be removed by an additional step before loading it in a sheet):

enter image description here

Here the M-Code generated by the UI.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
    #"Add Col: Col2 Text before delimiter" = Table.AddColumn(#"Changed Type", "Col2 TextBeforeDelimiter", each Text.BeforeDelimiter([Column2], "_"), type text),
    #"Add Col: Col3 Text after delimiter" = Table.AddColumn(#"Add Col: Col2 Text before delimiter", "Col3 TextAfterDelimiter", each Text.AfterDelimiter([Column3], "_"), type text),
    #"Added Custom" = Table.AddColumn(#"Add Col: Col3 Text after delimiter", "Check Number", each [Col2 TextBeforeDelimiter] = [Col3 TextAfterDelimiter]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Col3 Transformed", each if [Check Number] = true then [Column3] else [Column3] & "_" & [Col2 TextBeforeDelimiter])
in
    #"Added Custom1"

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.