Extracting info from cell

[SOS]

Hi all,

I receive a workbook on a monthly basis and it arrives in csv format. I need to format it and split it into several pages prior to sending to managers. I'm comfortable with the vba for formatting and splitting it into several pages but I have a problem with some cells that always have data in them in the same (incorrect) fashion. (Please see attached workbook for example).

I'd really like some help with how to go about fixing these cells and then I can get on with writing that into the macro.

Currently looks like:

A || B || C
No requested || No Supplied || Percentage
4 || 4100%
12 || 875%
6 || 350%

Should look like:

A || B || C
No requested || No Supplied || Percentage
4 || 4 || 100%
12 || 8 || 75%
6 || 3 || 50%

I was thinking of some kind of Left() formula to extract the leftmost character and place it into it's new cell BUT realise that sometimes I might need to extract 2 (or perhaps even more) characters and any help would be much appreciated.

Thanks

Seamus

[Shijesh Kumar]

How does the CSV file look from which u r importing the data... coz we need to check why column B is not coming

[SOS]

Hi Shijesh,

Attached is a cut-down version of the csv file. I've only cut out confidential data and left a section of how the data looks.

Many thanks

Seamus

[Leith Ross]

Hello SOS,

Here is macro that will separate the single number into two numbers. It assumes column "B" contains the value. Cell "B" will hold the integer value and cell "C" will hold the percentage as whole number. If the cells in columns "C" are already formatted for percent then the macro will need to be changed a little.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[SOS]

Sorry Leith,

That doesn't quite do what I need it to do.

Let me try and explain (hopefully more understandably).

This is a spreadsheet of bank nursing staff and should look as follows:

Col A is the number of nurses required for the shift, Col B is the number of nurses supplied by the bank and Col C is the percentage of the total.

Unfortunately Cols B and C get merged together during the download process and I have to try and separate them out.

So I end up with Col A being empty, Col B containing the number required and Col C containing an amalgamation of number supplied AND percentage all in one cell.

So if, for example 4 nurses were required and 4 were supplied I end up with "4" in Col B and "4100%" in Col C. I need to move the "4" from Col B to Col A and then split the "4100%" in Col C into "4" to Col B leaving "100%" in Col C.

That sounds easy if I only had to move the leftmost character in Col C but if the numbers were 20 requested and 10 supplied I would end up with:

Col B containing "20" and Col C containing "1550%" meaning I would have to move the leftmost TWO characters from Col C into Col B.

Does that make any more sense?

Thanks again

Seamus

[Leith Ross]

Hello Seamus,

I should have followed my own advice and downloaded the workbook, but I didn't because your example seem quite clear. The macro runs correctly now. Checking the results took longer than I had thought. Lots of special conditions. Here is the macro that has been added to the attached workbook.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[SOS]

Leith

Thanks for the reply. It's after 1am here in Scotland so I'll try your code tomorrow and report back

Seamus

[Leith Ross]

Hello Seamus,

Must be something about being Scottish and staying up late. Sleep well.

Leith

[SOS]

Leith,

That is absolutely fantastic. It works so well. Unfortunately I now have another question.

I'd like to adapt that code to fit another (very similar) scenario within the same workbook.

Remember that Col B had number required and Col C had number supplied merged with percentage. Your code dealt with that admirably.

Cols D and E are fine and need no changes.
Col F has merged number supplied and percentages. (I'll need to insert a col to accept the number supplied from Col F.
Cols G, H and I are the same prob as A, B and C (I can adapt the code for that).
Col J has number and percentage merged again and again I'll need to insert a column to accept the number supplied from Col J.
And finally Cols K and L are fine and need no changes

If possible can you tell me how to deal with these other problem columns

Many thanks

Seamus

I can attach a mocked up workbook if you want - just let me know

[Leith Ross]

Hello Seamus,

It would be a big help help to see the mock up workbook.

Thanks,
Leith Ross

[SOS]

Leith,

Doesn't matter. Managed to work it out - and by only deleting ONE line of code

Please Login or Register  to view this content.

It now works for all scenarios.

However (there's always a however).

What I have now is 4 blocks of code all very similar and I want to try and re-use code where possible so I'm looking at trying to condense the code and will let youknow how I progress. If I run unto too much difficulty I'll post again asking for more help.

Meanwhile thanks again for your help thus far.

Seamus

[SOS]

Leith,

Decided to post an updated sample workbook for you to see.

The advice I'd like is whether I can condense the code in module 1 because it seems that it is quite repetitive.

The other piece of advice I'm looking for I have also asked in this thread
http://www.excelforum.com/showthread.php?t=647437

and what I want to be able to capture is the address of the first instance of "Directorate NOT SET" and also the second instance of that so that I can offset the second address and copy that range out to another sheet. I then want to do the same with the first and second instances of "Cardiac" so I can copy that range out to another sheet.

As always any help would be most appreciated

Seamus

PS I've left an copy of the unformatted version in Sheet2 of the uploaded workbook for your testing purposes.

[SOS]

Sorry, forgot to attach the workbook in last post.

I also meant to say that I won't be able to search for the instances (ie "Cardiac" or "Directorate NOT SET") by name as each month the number of areas will differ therefore I need to be able to assign a variable to any cell in Col A that is duplicated and try to find out the 2 addresses so I can reference them to copy the range out to another sheet.

Hope this makes sense

Thanks

Seamus

[Leith Ross]

Hello Seamus,

Ack lad! Glad you attached the file. I am heading off to bed now, but will look at it in the morning.

Sincerely,
Leith Ross

[SOS]

Thanks Leith

It's morning here now!!

Must be something about being Scottish and staying up late. Sleep well

Thanks

Seamus

[SOS]

Hi Leith,

Been looking at trying to solve my problem and I think I've come up with a solution (albeit quite clunky). What do you think?

In Col A I have the list of all wards and each set of wards has a group heading both at the beginning and at the end of them ie:

Cardiac
Ward 1
Ward 2
Cardiac
Directorate NOT SET
Ward 43
Directorate NOT SET

and so on.

I need to grab the address of the first instance of "Cardiac" and also the last instance of "Cardiac" so I can offset the 2nd instance to be able to copy out a range to a new sheet.

The code I've come up with is as follows:

Please Login or Register  to view this content.

It seems to do the trick but can you suggest a sleeker way to achieve this.

Regards

Seamus