EXCEL Inserting Zero between letter and number

Question

Hello,

I am fairly new to excel and am having an issue trying to insert a zero after certain letters. For example I have this list:

H10

H1

V6

V56

V8

V999

H45

66

77

04

and I would like to sort it in ascending order to do so my logic was to write a function that will insert a zero after 'H' or 'V' if the length was less than 3 and the cell contains 'H' or 'V'

This is the function I wrote but it doesn't seem to be doing what I need it to do:

=IF(AND(OR(ISNUMBER(SEARCH("V",D1)),ISNUMBER(SEARCH("H",D1))),LEN((D1)<3)),REPLACE(D1,2,0,0),D1)

The function below puts a zero after all the H's and V's not the ones only with length 2.

What i want is:

04

66

77

H1

H10

H45

V6

V8

V56

V999

Thank you for all your help

Answer 1

Simple sorting will not give desired result, if you compare what is needed by user.

The below formula does it all.

Please let me know if it helps.

Answer 2

Thank you!

Answer 3

Re:  sorting

Excel sorts text by the first character in the sort column cells and then the second and so on.

From the sample you provided it appears that you can simply join the data and then sort by column "before".

My formulas won't be needed.

'---

Jim Cone

Answer 4

I need to concatenate it however with data from the column before which consists of data that looks like this:

A01

A03

N65

C07

Once I concatenate it then I need to sort it so my final sorted data should look like this:

A01-H1

A03-H10

C07-H657

N65-V5

etc..

Will this formula work for something like this.

Thank you for all your help

Answer 5

Re:  sorting by the numbers

Sort is done on column E...

'---

Jim Cone

Portland, Oregon USA

http://jmp.sh/K95N3ee