# Sorting Mixed Content - Numbers And Words

Mar 28, 2014

I have a worksheet with a row of mixed cells near the top. By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).

GOAL: I want to sort the columns to the following order:

First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.

So far all I have been able to accomplish is:

First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.

OR

First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.

This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.

I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.

Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.

I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.

## Sorting Mixed Text And Numbers?

Jun 17, 2014

A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.

Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.

Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.

## Count Words In Range Mixed In With Other Words

Nov 9, 2006

I have tried Sumproduct and Countif and can not get a proper count of rows that contain specific words in multiple columns. I have attached an example spreadsheet where I am looking for a result of 7 rows that contain 1 to 4 specific words within phrases. The example looks for the words: "virus", "spyware", "malware" & "adaware", and ignores any other words like "aware" or "ware".

## Hard Sorting Process / Dates And Supplier Code Mixed In Same Column?

Jan 22, 2013

I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:

9884A B Gerrish Ltd œ 577.270577.270
30/09/201231/10/20121865518655277.010277.01MAL/553260
30/09/201231/10/20121884218842264.260264.26MAL/553259
30/11/201231/12/2012195541955436036MEL/549200

so "9884" and "30/09/2012" are in the same column.

Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:

9884 A B Gerrish Ltd 30/09/2012 etc etc.

Obviously through out the document this repeats for each account, upto some 1000 accounts.

jas.xlsx‎

## How To Sort Numbers With Letter Prefixes And Letters Mixed With Numbers

Jan 21, 2012

Using the following data

R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8

I want it to sort like this:

R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8

What is the formula to achieve this?

## Compare 2 Columns For Numbers In Mixed Text & Numbers

May 3, 2006

I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.

## Auto-calculate 2 Column That Contains Numbers And Words Words

Jul 16, 2009

I've been using conventional method to do this and it's time consuming. I would like to total up 2 column. A multiply B to be exact. Below are some examples:

Table 1 - Before totaling up:

Quantity
Product
5
2 x Button A White
3
4 x Button B Pink
4
5 x Ribbon A Black
2
6
2 x Cloth A Blue

Table 2 - After totaling up:

Quantity
Product
10
Button A White
12
Button B Pink
20
Ribbon A Black
6
12
Cloth A Blue

I need to have the sum of the "Quantity" multiply "Product". Or in short A x B.
And the end result need to have the number and "x" sign removed while keeping on the the products names. (2 x ) Take note it's "number" space "symbol" space.

## Delete All Content Except For Some Words

Jan 24, 2010

I have a column containing mixed text and I want to remove everything from the column except instances of certain colors; black, red, blue etc. Is there a formula which would do this or can it be done with Search/Replace?

## Move Numbers Out From Mixed To New Column

Oct 20, 2011

Just wanted to do some work onto the form below, move the numbers out from the mixed colum and put in a new colum, then delete all the rows if empty in "style":

The original form is like below;

StyleGarmentFabric1001

BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40Recon Sports Bag600D polyester

What i need is like below:

Code

Style
Garment
Fabric

1001BS01Fugitive Backpack600D polyester1002BS05Cool Shuttle600D polyester1003BS07Cool Runner Backpack600D polyester1014BS13Xtreme Sports Bag600D polyester2018BS21Stealth Backpack600D polyester3008BS28Gear Sports Bag600D polyester3102BS40

Recon Sports Bag600D polyester... ...... ...

step by step:

stpe 1: move numbers out to a new "Code" colum;
step 2: delete the row if empty in "Style"

## To Sort A Column With Mixed Letters And Numbers

Aug 12, 2007

I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.

What I have is:

K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B

etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)

I need to sort them numberically from K1 in descending order ie:

K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc

## Compare String Of Words To Cell Content

Jan 31, 2008

I need to compare a list of keywords (stored in a string variable) with the content of a cell in order to see if any of the keywords do appear in the cell. I have to work with many such lists which can grow over time. What I'm doing is using InStr(cell with content, keyword) for each keyword. My goal is to have a macro which can automatically create the long formula from any list. My actual problem is that the formula I've build and stored acts more like a string. I'd like it to say either true or false. Anyway, here's a sample of what I came up with so far.

Sub build_formula()
Dim i As Integer, j As Integer, m(20) As Integer, n As Integer, z As Integer
Dim List As String, f(2) As String, s(20) As String, sp As String, Source As String
Dim Formula As Variant
List = "black, blue, green, red, yellow, white, z"
Source = Cells(3, 1).Value
z = InStr(1, List, "z")
i = 1
n = 1
Do
i = InStr(i + 1, List, ",")
m(n) = i ..........................

## Extracting Series Of Mixed Letters And Numbers From String

Oct 9, 2013

I have got to extract a series of mixed letters & numbers from a cell. The format of the data i need to extract is always [Letter][Letter][Number][Letter][Letter]][Letter][Number][Number]. The problem I've got is the notes field is not in a standard format as it can be anywhere in the cell.

The table below shows the notes cell & the Data I require.

Notes
Required Data
Fault number AB1ABC12 is complete
AB1ABC12
BC2ABC12 status is unknown
BC2ABC12
pending job ws1abc12
ws1abc12

## Sorting Certain Worksheets Based On Cell's Content

Jan 22, 2013

I have standardized each existing worksheets and any new worksheet to have certain parameters. The purpose of the Macro requested is ensure these worksheets are sorted alphabetically (and only these worksheets). The code I have been working with is contained below. It is working as expected but now I need to incorporate a statement which places the following condition:

Sort worksheets only if A3 has cell value of eCRF NameIgnore worksheet name: TOC & ENTER ECRF NAME

VB:
Sub Sort_Tabs()
'declare our variables
Dim i, j As Integer

[Code].....

## If X Number Of Words In Cell, Delete Cell Content, Else Do Nothing

May 26, 2009

This formula counts the number of spaces between words in a cell:

## Spell The Numbers In Words

Feb 13, 2014

I want to know about how to spell the numbers in words

## How To Format Numbers Into Words

Apr 9, 2014

I'm trying to format numbers in a cell into words. For example, if I type 1 in a cell, I would like it to show the word Optimistic. If i type 2 in the cell I would like it to show the word "Realistic", 3 to show "Pessimistic".

How would I be able to do that?

## Convert Words Into Numbers

Aug 31, 2009

For my research in medicine I build a spreadsheet with google docs. After having received most of the data I transfered the spreadsheet into an excel file. The next thing I would like to do is to convert the words in the spreadsheet into numbers so I can calculate with them. For example: every row in column E consist of one of the two words: 'man' or 'woman'. I would like to convert the word 'man' into a '0' and the word 'woman' into a '1'. Is it possible to write a formula or function for this?

## Converting Numbers To Words?

Dec 14, 2012

I'm having issues with my code for converting numbers to words I've tried a couple and I keep getting a Syntax error on

Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))

I've tried a couple of different codes and I keep getting errors

yes i have enabled macros

## Extracting Numbers From Words

Feb 7, 2013

In cell a1 I have the following:

Distance: 515 meters

In cell B1 I just want the numbers and want a formula that will extract them from A1 so in cell B1 it reads 515

## Numbers Into Words From 1 To 999,999,999 Only By Formula Not VBA?

Aug 22, 2013

EXCEL FORMULA CHALLENGE:Needs a formula like as above , expand the formula to generate from One to Nine Hundred Ninety Nine Thousand Nine Hundred & Ninety Nine? 1 to 999,999,999 all numbers into words between this range ?

## Turning Words Into Numbers

May 30, 2006

i would like to know how to turn the word beside into numbers, ie if b=2,e=5,s=60,i=10,d=4,

also can this word be in the one cell or would it have to be entered separately, ie one letter per cell??

## Convert Numbers To Words

Jan 17, 2008

how to convert figure into words eg 1250 (one thousand two hundred fifty only)

## Spell Numbers To Arabic Words?

Feb 18, 2014

I am using the following VBA to spell numbers in arabic words ! however , the solution resulted to having the numbers spelled to ASCII codes ..

Public Function SFormatNumber(ByVal x As Double) As String
Dim Letter1, Letter2, Letter3, Letter4, Letter5, Letter6 As String
Dim C As String

[Code]....

## Change Month (words) To (numbers)

May 5, 2006

I have a column with the month as the full word (January or February, etc.).
I need to translate the words to numbers in a separate column (1 or 2, etc.)
I haven't been able to find a function that can help perform this task. "If"
statements will not go further than 8 levels. The month function will not
recognize it because it's not part of a full date. What other options do I
have? I feel like I'm missing something obvious here...

## Total Of Two Columns Containing Numbers And Words

Jul 10, 2007

I am attempting to create a chart from data generated outside of Excel 2003 SP2, that has been Copied and Pasted into Excel. There is no metadata associated with this data. I have created a list with the data once it has been pasted into Excel 2003 SP2.

The Workbook has five (5) worksheets, all with the default names (Sheet1, Sheet2, etc.). I paste the data into Sheet1 and reference this from the other worksheets. Sheet2 shows the charts for all the data. Sheet3, Sheet4, and Sheet5 are each for different regions, North, South and West respectively. I would like to enter the formula into Cell: Sheet5!M5 so it auto populates with the appropriate and correct information. Sheet1 information is as follows;

Column B has a header of "CreateDate" and is in the format of; 4-Jun-07
Column O has a header of "Business Unit" and has three (3) possible answers, North, South or West. **I have attached a screen shot of the two colums used named, "Columns.Used.png". I have hidden the columns in between as they contain confidential information.**

I would like a formula that gives a total of one if both criteria are met. The criteria is as follows;

IF Column B on Sheet1 equals Cell L5 on Sheet5 AND Column O on Sheet1 equals West THEN the total should appear. **I have created and attached a sample spreadsheet "Sample.Data.zip"that contains only basic data on Sheet1 and Sheet5 as those are the only sheets relevant.**

## Converting Numbers (Money) Into Words?

Mar 8, 2009

Any easy function to convert numbers into words?

I a looking for an easy function to transform numbers into words as shown :

Value------Output :

12624-----Rs Twelve thousand six hundred and twenty four only
12624.24--Rs Twelve thousand six hundred and twenty four and twenty four paise.

Hopefully the currency should be changeable ( dynamic) i.e Rupees to Dollars..

In India , we also use lacs and crores , so something in those lines if possible...

## Converting Numbers Into Words With Hyphens?

Mar 3, 2014

I need a way to randomly generate a number( up to hundred trillion) in cell A1 and in cell B1 have the number appear in word form. Example: 129,114,023,131,453 will appear in A1 and in B1: one hundred twenty-nine trillion one hundred four-teen billion twenty-three million one hundred thirty-one thousands four hundred fifty-three The hyphens are important, commas between the numbers are not necessary. I know that for A1 I can use Randbetween() function to generate the numbers. The problem is generating the word form with the hyphens....

## Extract Numbers After Specific Words?

Mar 13, 2014

I am trying to extract numbers after a specific text in a text, for eg :

abc SN 12345 xyz
edf SN No. 456 mno

As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)

Any formula to get result as "12345" and "456".

## How To Convert Numerical Numbers To Words

Jun 20, 2014

how to convert numerical numbers to words in MS excel & MS world

## Changing Numbers From Numerics To Words

Jun 20, 2006

Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?