Align cells with same characters



I have an excel file with two columns and I want to put on the same line the cells which starts with the same characters (example : 15896). How can I do ?

There is a screenshot of the type of data I have and the results I want to have.

I put differents values in colors so that you will better understand what I need.

Thanks a lot for your help




Please look at the attached file.  It's a start. Once you follow the logic, you can create the 2 columns as you like.

Earlier answer:

1. What do you mean by 'Align'? Elaborate!  (there are 3 basic alignments: Left, Center and Right - still not sure what you mean)

2. Your example is not easy to follow

3. Read forum rules, Show data and Results (how it should look)

Redefine your issue more precisely please



I checked the file, thanks.

Sorry but I forget to mention one thing : infact, I use two extraction tools to obtain datas, respectively for column A and columb B. Theoretically, all must match.

So I match this on Excel, and I see that sometimes, one value is missing in column B (it happens around one time every 50 values, so I must rearrange and synchronize with column A)

Of course, I can repair it manually but I have lists of thousands of values so it will be way too long.

Do you understand my case ?
pierrecima Nov 15, '17 at 10:41 am
Add to Discussion

Please try this formula. Paste it in a blank cell in row 2 and copy down.

=IFERROR(INDEX($B$1:$B$3200,MATCH(LEFT($A2,FIND(" ",$A2))&"*",$B$1:$B$3200,0)),"")

Answer the Question

You must create an account to use the forum. Create an Account or Login