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
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
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
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.
