CODEWITHSUNDEEP
excel
kunz
kunz

Reputation: 1047

Sorting in to columns

I need to put the date of column A in Column B. This is just a sample, I have more than 1,000 records.

enter image description here

Upvotes: 0

Views: 18

Answers (1)

Brino
Brino

Reputation: 2502

You have three options:

Option 1: Import and Parse your Raw Data

It looks like your data is delimited by the pipe (|) character. You can import your raw data file into excel, and parse it. Use the following steps to import your data. This will create columns for your ID, Date, Name, and Info fields.

  • On the DATA ribbon choose "From Text" in the "Get External Data" section.
  • Choose your raw file
  • Choose Delimited
  • Check "My Data has Headers"
  • Set Delimiters = Other "|"
  • format any columns if needed

Option 2: Parse your existing data in column A using Text to Columns

As suggested in the comments by @Scott Craner, you can use the "Text to columns" option in the Data tab to parse your data using the following steps:

  • Select column A
  • Click "Text to Columns" in the Data tab
  • Under Delimiters, uncheck everything except "Other" and set the character to "|".
  • Click next, then format your columns as desired. You can click on your second column and format it as a date if you wish.
  • Click finish, and then OK to paste the data in place.

Option 3: Parse your data using a formula

If you insist on using a formula to parse your data in column A, you can paste the following formula into cell B1 and copy down:

=TRIM(MID(A1,FIND("|",A1,1)+1,FIND("|",A1,FIND("|",A1,1)+1)-(FIND("|",A1,1)+1)))

This formula looks for the first and second occurrences of the pipe (|) character, and grabs the text in between. It then uses the Trim() function to remove whitespace before or after the text.

Upvotes: 1

Related Questions