Solved

Excel formating cell/ formula help

Posted on 2014-11-19
7
98 Views
Last Modified: 2014-11-19
Hi guys,

I'm a bit confused about how to format a cell of which formula to use to have the expected end result.

I have a few cells like this:

6491
7620
6486
7610-8
0888-8
0880-9
0888-8
894
890
898

and i would like add a fixed number in between which is 984100, so it would look like this:

64919841000
76209841000
64869841000
76109841008
08889841008
08809841009
08889841008
08949841000
08909841000
08989841000

So those cell that has only 4 digits will have a extra 0 in the end, and those that has a number 1 to 9 in the end will get the last number in the end and those that only have 3 digits will add a extra 0 in the beginning and a extra 0 in the end.

so like this:

6491 = 6491 + 984100 + 0  = 64919841000
and
7610-8 = 7610 + 984100 + 8 = 76109841000
and
894 = 0894 + 984100 + 0 = 08949841000


Hope this made sense
0
Comment
Question by:Hakum
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
=IFERROR(LEFT(A1,FIND("-",A1)-1) & "984100" & RIGHT(A1,1),IF(LEN(A1)=3,"0","") & A1 & "9841000")
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Could you ever have only 3 digits to the left of the hyphen? (e.g. 888-8 rather than 0888-8)
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Awesome!
This works although there is one thing, those that only has 3 digits in the cell it need to add a 0 in the start aswell... how to do so?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Author Comment

by:Hakum
Comment Utility
Rory, yes that is possible that it may happen
0
 
LVL 18

Expert Comment

by:SimonAdept
Comment Utility
I should note that the IFERROR() function only works on Excel 2007 onwards. Example workbook attached. If you need this to work in older versions, it can be rewritten to use backward compatible functions.
EE.xlsx
0
 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
Comment Utility
Taking note of the 3 digit value followed by hyphen:

=IFERROR(IF(LEN( LEFT(A2,FIND("-",A2)-1))=3,"0","") & LEFT(A2,FIND("-",A2)-1) & "984100" & RIGHT(A2,1),IF(LEN(A2)=3,"0","") & A2 & "9841000")
EE.xlsx
0
 
LVL 1

Author Comment

by:Hakum
Comment Utility
Thanks alot SimonAdept! works like a charm!

Thanks alot for quick help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now