# Concatenate Text And Numbers And Then Sort On Original Number

Jun 26, 2013

I have a data set that shows numbers for categories over time. I would like to add the data label to the numeric value and then sort the data by the original number. Is this possible?

Here's an example

A 1 2 3 4
B 5 7 4 1
C 8 0 2 1
D 3 4 9 5

I would like each column sorted independently based on the data in that column, but I want the results to have the label from column 1 concatenated with the value in the other columns such as this:

A1 C0 C2 B1
D3 A2 A3 C1
B5 D4 B4 A4
C8 B7 D9 D5

Each column is sorted by the numeric value, but has the name label from column 1 attached to it.

## Sort Mixture Of Numbers And Text Using Numbers And Decimals

Nov 13, 2013

I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)

Code:

Col A Col B
1Billing Accuracy
2Billing Time
3Credit Check Accuracy
4Credit Check Time
2.1Bill preparation

[Code]....

This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (8.1.2.1 through 8.1.2.3) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.

## Rank & Sort Table: Unique Numbers Sort Ascending, But The Non-unique Numbers Sort Descending

Oct 5, 2007

I have a list in rows where I have a ranking formula =COUNT(\$G\$5:\$G\$81)-(RANK(G5,\$G\$5:\$G\$81)+ COUNTIF(\$G\$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending

ex) 1.751
2.52
3.753
418
417
416

## Sort Numbers As Text?

May 28, 2013

I have a column of data that contains alphanumeric ID codes, some of which contain no alpha characters (i.e., Excel treats them as numbers). When I do a data sort, Excel puts all the purely numeric codes first, then the alphanumeric codes. I want to sort the numbers among the alpha codes.

Example Excel sort order:
06090
10400
28198
34078
43321
0BKV9
1C7K4
2P776
3BTW3
4U744

Desired sort order:
06090
0BKV9
10400
1C7K4
28198
2P776
34078
3BTW3
43321
4U744

I know there's a way to do this, but I've spent half an hour looking and can't find the answer. And no, manually editing each numeric entry with an apostrophe is not an option.

## Sort Unique, Then Copy From Original List

Mar 1, 2004

I have a CSV spreadsheet full of data: many rows and many columns. I want to sort based on values in some column. Then, I want to divide my large worksheet into smaller worksheets based on these sorted column values. For instance, if column 4 was 20,000 items long and contained 6 unique values I would want to create 6 smaller spreadsheets. Each spreadsheet would still have a column 4. Now, however, all the column 4 values would be the same for a particular spreadsheet. It would be nice to have a pop-up window query me for which column to use to divide the data with, instead of hardcoding a value in (like 4).

I am trying to crank this CSV file through MATLAB and it is just too big. My PC has 1GB memory and it still craps out. I am guessing that a dozen or so smaller files will be easier for MATLAB to digest. Any help is appreciated!

For the record, I am doing the following to sort a column, then copy it to a scratch worksheet and then determine all the unique values. I have hardcoded in column lengths and would really like to know how to make these variable expressions:

Sub divide_and_conquer()

Dim lngSheet As Long
Dim strName As String

Range("A1:HR778").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("B2"), Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _.............

## Sort AlphaNumeric Text By Numbers

Dec 6, 2006

Take for example ABC10 in Cell A1, ABC199 in cell A2 and ABC9 in cell A3

How am i able to sort such that ABC9 appears in cell A1 followed by ABC10 in cell A2 and ABC199 in cell A3?

## Sort Numbers Appended With Text

Aug 17, 2007

I've got data in a table that has am induvidual "job number" assigned to each row, but this job number could have a variation such as 1000A 1000B 1000C but they may not necessarily be one after the other in the table (other job numbers may appear in between) so i need a to macro to remove all job numbers lets say that start with 1000 and paste them and the end of the table. so i end up with consecutive numbers. Can this be done? I've played around with the find funciton to loop through the column and find all relating job numbers then cut the entire row but cant quite seem to get it to work

## Sort Column Of Number &amp; Numbers With Alpha Characters Attached

Jan 7, 2010

Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.

Example:

376
377
421
376AB
376XY
377NC
421GQ
421EF

Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:

376
376AB
376XY
377
377NC
421
421EF
421GQ

## Sort Numbers Stored As Text Not Working?

Sep 17, 2013

In a spreadsheet I have a text column with entries such as:
2012
10 Times
10.5 Times
101 Times
25 Times
A Pure Text Entry

When I sort by this column I expect to get:
10 Times
10.5 Times
101 Times
2012
25 Times
A Pure Text Entry

Instead I get the 1st order. Why? I've selected 'Sort numbers stored as text' separately so it shouldn't be treating 2012 as a number. Besides I've checked & the cell formatting is Text.

## Sort Column With Equal Numbers And Linked Text

Jun 8, 2014

I would like to create a formula that will sort the numbers in column B, there are also equal numbers in this column

The difficult part is that there is text in column A, and is linked to the numbers

So if numbers are sorted the names should be sorted in the same way as well.

## Highlight Entire Row When Cell Selected Without Losing Original Formats And Color Of Original Row

Sep 5, 2012

The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors

Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices

[code].....

How can I retain the range's historical color so that when I deselect the row it reverts properly?

## Show Only The Number Of A Cell Containing Text And Numbers

Nov 23, 2009

Lets say cells A1 to A5 contain these lines of information:

Till 174 (T0215) - till keeps turning itself off.
Till 245 - stuck on windows screen
116 - keyboard is unresponsive
Berkel Scale is constantly beeping
ped not reading cards Till 156

How can I show only the numbers from these cells (i.e. B1 will be 174, B2 will be 245 etc...)? As you can see the number isn't always in the same place, and doesnt always have the same characters either side.

## How To Come Out With Random Number From A Field That Have Text And Numbers

Nov 7, 2013

I need excluding text from a cell and come out with a random number up to 3 numbers. Please see the attached image;

If you notice in column D there is text and a number that starts with a decimal, for example on the first row is .437 the next row is .215 next row is .63 etc... Is there a way to come out with a random number mixing it up to 3 digits and output those number on column "E"? Im only interested in filtering and coming out with the random number i mentioned .437 .215 .63 etc... not the +/-.010in.

There is no right or wrong number, i just need to come out with random number using up to 3 numbers up.

Maybe for .437 three numbers up could be .440 for the next row .215 maybe a number up .216..etc..

Hope there is a way to do this, my excel file is too long to type a number manually.

## Extracting Specific Number From Text Containing Other Numbers

Apr 27, 2007

I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.

## Sum With Multiple Criteria A Number Of Rows And Columns From Original Data

Sep 22, 2006

I have 6 columns of data laid out as follows,

columnA columnB columnC columnD columnE columnF
XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

XXXXXXXX XXXX XXXXXX XXXX XXXX XXXX XXXX XX

I need to find data in columns B and C and add the figures from column F, which drop down one line. I have written the following formula which is giving me far higher figures than I should be getting

= SUMIF(\$B\$2614:\$B\$2640,"bhree*",IF(\$C\$2614:\$C\$2640,">=01/07/2006",( OFFSET(\$C\$2614:\$C\$2640,1,3,1,1))))

## Conditional Formula: If Text, Repeat As Number To Sum, Else Sum Numbers.

Dec 29, 2008

I can get only so far, then stumped:3 columns (Hours, Rate, Amount). "Hours" is a 'List' with data from from another sheet. Data is named 'Worked'. 'Worked' is all 2 decimal numeric (represents total time worked), except first item called "Live In"
IF 'Hours' is "Live In", I can use: =IF(A1="Live In", SUM(B1*1))

How can I make it conditional so that if it's not "Live In", then it will SUM(A1*B1)?. SideNote: 'Worked' LIST has total time with minutes expressed as 1/4 of hour (i.e., 1.25 = 1 hour, 15 minutes).

## Converting Text To Numbers To Useable Number Format

Sep 29, 2008

Is there anyway of converting the output (which is 7 and in text form) to a useable number format so that I can take advantage of the VLOOKUP function (instead of getting a #N/A)....

## Cannot Convert Numbers Stored As Text To Number Format

Dec 14, 2009

There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:

"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.

The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.

## Text To Columns :: Use Function When Original Cell Has More Than One Line

Oct 10, 2007

I want to use the text to columns function when the original cell has more then one line (lines are separated with Alt+Enter). Somehow Excel does only notice the first line.

cell A1:
Bookrunner: Lehman Brothers;
Mandated Arranger: CapSource Financial Inc;
Participant: Citibank NA

(After the ";" is an ENTER)

And i want it to be like this at the end:

Cell A1:
Lehman Brothers
Cell B1:
Bookrunner

Cell A2:
CapSource Financial Inc
Cell B2:
Mandated Arranger

Cell A3:
Citibank NA
Cell B3:
Participant

## 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?

## Custom Format That Includes Text And Numbers, Such As Medicare Number

Jan 19, 2006

how can you custom a cell format in which you may in type in information that is displayed as ###-##-####-@# I searched everywhere and cannot find this.

For example, a medicare number.

I would need to type in 581566223b6, and it should be displayed as 581-56-2236-B6.

## How To Sort Item Numbers Targeting Only Specific Numbers

Dec 28, 2013

How can you sort item numbers, targeting only the numbers in the mock up file?

Example:

item number: GW14SO0000003
item company: GW
item location: 14SO
item counter: 0000003

How can I only sort the item by the last 7 digits "0000003"

## Re-establishing Formulas On “text” Input For A Specific “cell” = Original Formula Act

Nov 23, 2009

See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?

In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘\$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?

## Concatenate Numbers With Comma If 2 Other Conditions Are Met?

Mar 31, 2014

I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.

Let's say, my numbers are in Row 1, from B1 to N1.

My conditional values are in Rows 5 and 6 respectively, also from B to N.

Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.

I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.

Row 1: 1 2 3
Row 5: Name Name Name
Row 6: Date in Feb, Date in March, Date in March

Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:

Name+March -> 2, 3
Name+February -> 1

## Concatenate Cells Into Unique Numbers

Mar 20, 2008

I need a mcaro to concatenate my project. I have 1 number in six separate cells that I need to be concatenated into a 3 digit number without duplicates. My data is in cells A4:C5 (six numbers) note that I have two duplicate numbers so the results should only yield (10) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly.

My data in cells A11:C12 (six numbers) are all different so the results should yield (20) different 3 digit numbers, none repeated. The results could be displayed in one or two columns evenly. I also wanted to know will I be able to edit the macro to concatenate 5, 6, 7, 8 or even 9 numbers into 3 digit numbers without duplicates. If so, how can this been done? The results would be 5#'s:10 results; 6#'s:20 results; 7#'s:35 results; 8#'s:56 results;

9#'s:81 results.

I have included a sample file.

## Advanced Concatenate With Spaces Separating Numbers

Jan 13, 2009

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).

Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this :

=CONCATENATE(B4:B127)

How do I add spaces to a range?

My problem is very similar to the question asked in this thread [URL] .... but I am using numbers and would like to separate them with spaces.

## Advanced Concatenate With Spaces Separating Numbers

Jan 13, 2009

I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9).
Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range?

my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html
but I am using numbers and would like to separate them with spaces.

## Excel 2003 :: Concatenate Formula With Numbers With Decimals

Jul 12, 2012

I'm using excel 2003 and have a problem regarding some code.

Dit(a, b) = "=" & Hit(a, (d - 12 - e) + f) & "/" & (Pro & ".NrE.sol")

Where "Hit(a, (d - 12 - e) + f)" can be numbers with decimals.

When I run the code the result is nothing, unless the number is a number without decimals.

If I use just "Dit(a, b) = Hit(a, (d - 12 - e) + f)" it shows the right number.

## Concatenate The Number Into A Formula

Oct 14, 2008

I need to do a vlookup with the array starting on a row determined in another cell, so I've tried concatenating the number into a formula but this doesn't seem to work. Is there something else that I can do?

E2 is 134

=CONCATENATE("=VLOOKUP(\$B2,\$C\$", E2+1, ":\$F\$2571,3,FALSE)")

returns: =VLOOKUP(\$B2,!\$C\$135:\$F\$2571,3,FALSE) but not as a formula, as text and I need the formula...

## Concatenate Values With Same Number

Feb 6, 2010

I need VBA that will make those two columns:

1 A
1 B
2 C
2 D
2 E
2 F
2 G
3 H
3 I
3 J

into those two columns:

1 A,B
2 C,D,E,F,G
3 H,I,J