LEFT and IF to make up to 10 characters

horizonflame

Board Regular
Joined
Sep 27, 2018
Messages
172
Hi in Column A, I have a mixture of 7 and
10 character long project numbers.

In Column B I want to reference all project numbers to a uniform 10 character format. With the 7 characters ones I want to add “/04” to them.

Can this be achieved with a LEFT and IF formula?

Thanks
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Sure, perhaps something like this

=if(len(a1)=7,"/04","")&a1

This assumes you want to add "/04" as a PREFIX.

If you want to add it as a SUFFIX, try

=a1&if(len(a1)=7,"/04","")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,120
Messages
5,466,809
Members
406,499
Latest member
ToyoMike

This Week's Hot Topics

Top