Paste special problem in Excel

[RosieMP]

I've been stuck on this for months--can anyone help?

We manipulate a lot of data that comes out of SAP/BW. The BW stuff is crazy with formatting--much of it specifically formatted as text (with a little ' mark in front of it.)

There is nothing I can do about this. It's something to do with how they do exports, and I think if I tried to explain the problem it would sound trivial but it's actually pretty disruptive for a dumb little problem.

The problem is when we try to do matching formulas with other, non-formatted spreadsheets that don't come from BW. It is very, very difficult to get a match on (let's say) serial number, when in one spreadsheet it's formatted as text and one is a number -- vlookups fail, and vlookups are our corporate currency.

Here are things that have been tried and failed, or work sometimes but not consistently:

1) copy and paste special
2) multiplying by 1 to force create an integer
3) =int(clean(trim(a1)))--and all variations thereof, e.g,
=trim(a1), =clean(a1), etc.
4) copy and pasting special to a new workbook
5) all manner of formating the columns to match data type--it "works", in that you don't get an error if you change the data type, but somewhere deep down, Excel persists in seeing an integer as text or vice versa.

This is the horror I resorted to last time: I copy pasted special to a new workbook, saved it as a .txt file, then opened in Excel, then changed the SN column data type to text in the import wizard(which was what my matching file was hoping for). That worked.

The issue is not always so hard to deal with -- sometimes a copy paste special to a new worksheet will drop the formatting (I just did this, and it even dropped the ' mark) but it's inconsistent.

We'd like to get more people to be self-sufficient with some simple Excel formulas but are having a hard time getting past these stupid formatting issues.

I know VBA well but haven't found anything within VBA that does anything beyond what the manual attempts above do. I'd be open to doing a 'clean the data' type macro, but don't know what I'd ask a macro to do that I haven't already tried manually.

Anyone have any suggestions?

Thanks
Rose

 

[xlbo]

Pretty much the "Muliply by 1" should always work to convert text to number. If it doesn't, I would expect the data to not only have a ' in front of it but also spaces at the end in which case you could use =value(clean(trim(A1)))

The other option that pretty much always works is Data>Text To Columns

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RosieMP]

I tried that text to columns idea but the tick mark doesn't appear in the wizard -- you can see it in the cell only in the formula bar, but not in the cell just by looking at it, and it doesn't appear in the wizard at all (no leading spaces either). (Good thought, though.)

The multiply by one option does sort of work -- you can multiply by one and return something numeric because you can see decimals returned (like, 1.00) but the vlookup still won't match against a numeric column on a number sheet.

Value/trim/clean worked (thank you!) if I used it on a fresh worksheet--which is good, but I still would like to be able to do it on the worksheet that I'm currently using without having to move off the sheet. There's something about what BW outputs and how Excel behaves with that that makes it impossible to just manipulate it all on a single file.

Are there any other sneaky tricks around this?

Thanks

 

[xlbo]

to be honest, the multiply by 1 and the text to columns are the best ones to use

I know about the tick mark not appearing in the cell - only in the formula bar - it's a text marker rather than a character in its own right

After you have used the multiply by 1 fix and see the decimals returned, what issues does the lookup have? what error does it return? can you post an example of the lookup formula you are attempting to use ?

Use the ISNUMBER or ISTEXT formulae on each set of data to ensure that excel does think of them as the same data type. If it returns true for each data set then there is something else the matter other than just data types

I used to use SAP in an old job and I remember the exports being a pain but I recall that they could be cleaned up with not too much bother

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RosieMP]

I don't get an error on the vlookup, I just get nothing returned.

I use an if/iserror/vlookup combo for vlookups so I don't get '#value' if there's nothing found, so the basic configuration is, if it's an error, return the value "not found", if there's no error, then return the value I'm asking for.

In the case of the ones that "don't work", I get "not founds", even though they clearly should be found.

Let me root around for the example (I dealt with this last week for a specific report and wound up using the import function as the fix). I'll try to find and post later today.

The SAP stuff is generally a pain, but like you said, usually they can be cleaned up without too much bother. There's something about this particular report that resists all the usual tricks and makes it hard to pass on the process of building the reports to a casual user.

Thanks for your help; I'll try to dig out the example in question later today.

 

[Welshbird]

How about using =value(c2) in a spare cell? I use that in combination with copy =>paste special =>values.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]