Excel – How to copy specific selected data between cells in a CSV file in Excel or Notepad++

cellscsvmicrosoft excelnotepadspreadsheet

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.

Best Answer

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
Related Question