adding stripped info in a column to equal x length

G

Guest

Hello. Does anyone know of a formula to help me acheive the following?

Column A has:
123
198453
5
11192

I need column B to become:
MH1000123
MH1198453
MH1000005
MH1011192

Any help would be appreciated. Thank-you!
Kyli
 
G

Guest

Sorry about the double post. I received an error on my first one stating it
didn't go through.
Kyli
 
R

RagDyer

And, if column A might contain text:

="MH1"&REPT(0,9-LEN(A1)-3)&A1
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi
in B1 enter
="MH" & 1000000+A1
and copy down
 
G

Guest

Frank, you are always so helpful. It worked perfectly! Thank you for your
help!
: )
Kyli
 
G

graphguy

This will do the trick:
=SUBSTITUTE(B18,B18,"MH1"&REPT("0",(6-LEN(B18)))&B18)



colB
18 123 MH1000123
198453 MH1198453
5 MH1000005
11192 MH1011192
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

adding stripped characters to equal x length 1
How to check for a date range match in one column and then count thevalues equal to in another 5
Counting Unique occurences of text in a column 12
Copy rows from Sheet1 to Sheet2 when Column B values equal "X" andColumn C values > 100 2
How to add values in one column based on values in another column 6
Formula to find last different value in a column? 5
Matching column criteria in a one to many relationship 8
Adding values across a row based on a corresponding column value 9

Top