manipulating data mid left

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Manipulating Data (mid, left, substitute functions.

Manipulating Data (mid, left, substitute functions.

resolvedResolved · Urgent Priority · Version 2003

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.


 

Excel tip:

Paste functions box quickly

If you want to do a formula using the paste functions box press SHIFT + F3.

View all Excel hints and tips


Server loaded in 0.09 secs.