TransWikia.com

Does excel provide a method for sorting columns based on numbers and phrases contained within one cell?

Super User Asked by Friend to Dogs on November 4, 2021

I receive a packet of data in the form of a .csv file from our database manager. I import this data into excel for formatting and sorting. However, one column contains three pieces of info that I want to parse and sort. A single cell in this column consists of a user ID number, and a user name
(for example: "83920 – johndavis") I want to be able to sort this column based off of each of these terms. Is this possible using macros in Excel or should I use VBA, and if so how do you suggest approaching this problem?

I have a background in C and python so despite a limited VBA knowledge I have computer science experience.

Here is some info I collected while experimenting with solutions. This page was my starting point: https://www.cedarville.edu/insights/blog/excel-how-to-parse-data-split-column-into-multiple.aspx
However, when I attempted to use this method, the column split encountered errors and didn’t split properly (see pictures). I believe this is because the column data is actual data manipulated in VBA from another page of the excel sheet. How would you go about resolving that idiosyncrasy?

Data as seen from the function bar

Page 1 of the Column Split Wizard

Page 2 of the Column Split Wizard

Page 3 of the Column Split Wizard

Incorrect Column Split Wizard Output

3 Answers

Per my comment to your post, you can do this instead:

enter image description here

I would also note that your image showing a reference to a VBA function prefixed with @__xludf. likely means that the function is not available to the current workbook. It may be in a different module on another workbook, or in an unavailable library.

Regardless, in simple cases with part1delimiterpart2, you can use the formulas above.

In more complex cases, you can select any cell in your data and use Data>Get & Transform Data>From Table/Range to start a PowerQuery.

Within the Power Query Editor, you can select the column you want to split and use Home> Transform>Split Column>By Delimiter and configure it like this (for example):

enter image description here

The output looks like this: enter image description here

You could then use Home>Close & Load to load the results back to the workbook.

Answered by FlexYourData on November 4, 2021

That's a rather weird CSV as it contains Excel formulas. It's not expected that a CSV contains anything else than data.

Currently the cell doesn't just have the data value B09 - Theater, but it has the formula:

=IFERROR(@__xludf.DUMMYFUNCTION("""COMPUTED_VALUE"""), "B09 - Theater")

The IFERROR formula evaluates the (dummy) function @__xludf.DUMMYFUNCTION("""COMPUTED_VALUE""") and if that returns an error, it writes the value B09 - Theater.

If you don't need to maintain those formulas (I don't know if they serve a purpose) and just need the data the easiest would be to select all cells and paste them in a new sheet as values.

After that you can use the answer of iskyfire.

Answered by Saaru Lindestøkke on November 4, 2021

Convert Text into Columns

If you have a cell that contains multiple data values, you can separate them using the built-in Convert Text to Columns Wizard. This will create a table with 1 data value per column which will enable you to use all the filters and sorting methods that are available with excel.

Note: If you have multiple unique delimiters separating each data value, for example 83920/johndavis - new york city you can use find and replace to change all of the - characters into / or vice versa. You need to have the same delimiter separating each data value or you will need to run the wizard multiple times.

See https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7 for additional information.

Removing Extraneous Functions

If the data that you have is encapsulated, you can use Find and Replace with Match entire cell contents checked and Look in: Formulas selected.

Select the range, go into Find and Replace, make your entries and Replace All. For Example =IFERROR(@__xludf.DUMMYFUNCTION("""COMPUTED_VALUE""")," to an empty string.

Then you can run it again to remove the ending part ") to an empty string.

Ensure that before making large-scale find and replace operations, you have backed up the original copy of the worksheet.

Answered by iskyfire on November 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP