98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Manipulating Data (mid, left, substitute functions.
Manipulating Data (mid, left, substitute functions.
Resolved · Urgent Priority · Version 2003
Mon 26 Oct 2009, 14:20Delegate Gareth said...
Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
Manipulating Data (mid, left, substitute functions.
Hi
im looking to manipulate some data within a cell.
In cell B2 I have a number.
42JD0128508
Using the formula below I can manipulate the data to
=LEFT(B3,4)&"/" & MID(B3,6,4)&"/" & RIGHT(B3,2)
42JD/1285/08
However I need to add something to my formula that would replace or remove the 6th character of the text if this was a "0"
The above example has "1" so thats no problem however if that number was a "0" it would cause problems and hence I would like to get rid of it.
Any help?
Thanks in advance.
Gareth
Mon 26 Oct 2009, 14:30Trainer Amanda said...
RE: Manipulating Data (mid, left, substitute functions.
Hi Gareth
Thank you for your question.
On the surface this looks like a situation where you might use an IF function - the question is, if the 0 was to be replaced (as you refer to it being replaced or removed), what would it be replaced with?
Kind regards
Amanda
Mon 26 Oct 2009, 14:49Trainer Amanda said...
RE: Manipulating Data (mid, left, substitute functions.
Gareth,
Thinking about this a bit more, would it always be the case that the 5th and 6th are both zeroes? In which case could you use the Replace feature to replace 00 with 0?
Amanda
Tue 27 Oct 2009, 10:47Delegate Gareth said...
RE: Manipulating Data (mid, left, substitute functions.
Amanda
Thanks for your reply.
I managed to find a solution that does exactly what I am after. Below is the formula that I have used, the IF function was the solution
=LEFT(B3,4)&"/"&IF(MID(B3,6,1)="0",MID(B3,7,3),MID(B3,6,4))&"/"&RIGHT(B3,2)
Thanks again.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Paste functions box quicklyIf you want to do a formula using the paste functions box press SHIFT + F3. |
