Solved Copy part of a cell in Excel

January 21, 2017 at 06:25:18
Specs: Windows 64
I have used an email parser to copy part of an email containing an invoice into excel. I have thousands of entry's. I need to calculate the total VAT amount and I think the only way to do that is to copy the VAT amount into a separate column. Some of the cells have more than one address so I cannot search by exact position. Cells looks like this:

"Number: CPLUS0000000000
Date: 01/01/2016

Address Amount Tracking code

Customer Name
Customer Address
Town
City
£4.62 XX000XX

exVAT: £3.85
VAT: £0.77
TOTAL: £4.62

===========================================

© 2016 Drop and Collect Ltd. trading as CollectPlus
Registered Number: 06593233 | VAT Number: 946830691 | "


See More: Copy part of a cell in Excel

Report •

✔ Best Answer
January 21, 2017 at 10:18:21
Here is another idea that seems to work, kinda OK.

Use the Import Text function:
On the Ribbon,
Select Data Tab
Select From Text in the Get External Data section
Select the name of your text file that contains your data.
The Text Import Wizard should pop up
Select Delimited
Click Next
In the Delimiter section select Other
In the input box enter: ALT+156
(Press & hold the ALT key while imputing the numbers 156)
You should see the British Pound symbol appear.
Click Next
Click Finish
A small Import Data box should appear, Click OK

Your data should now appear like:

                         A                                          B
 1) "Number: CPLUS0000000000                               
 2) Date: 01/01/2016                                       
 3)                                                        
 4) Address Amount Tracking code                           
 5)                                                        
 6) Customer Name                                          
 7) Customer Address                                       
 8) Town                                                   
 9) City                                                   
10)                                                            4.62 XX000XX
11)                                                        
12) exVAT:                                                             3.85
13) VAT:                                                               0.77
14) TOTAL:                                                             4.62
15)                                                        
16) ===========================================            
17)                                                        
18) © 2016 Drop and Collect Ltd. trading as CollectPlus    
19) Registered Number: 06593233 | VAT Number: 946830691 | "

Just delete the string "XXOOOXX" in cell B10 and
all you numbers are now separate from the rest.

See how that works for you.

MIKE

http://www.skeptic.com/



#1
January 21, 2017 at 07:41:26
I have tried: =RIGHT(A2,171) in column B and =LEFT(B2,4) in column C and I have a result, but I cannot use auto sum on the result to give me a total...

Report •

#2
January 21, 2017 at 07:43:23
If all your data is the same as your example:
             A                        B
 1) "Number: CPLUS0000000000
 2) Date: 01/01/2016
 3) 
 4) Address Amount Tracking code
 5) 
 6) Customer Name
 7) Customer Address
 8) Town
 9) City
10) £4.62 XX000XX
11) 
12) exVAT: £3.85
13) VAT: £0.77
14) TOTAL: £4.62
 

In cell B10 enter the formula:

=IF(ISERROR(MID(A10,FIND(CHAR(163),A10,1)+1,FIND(" ",A10,1)-1)*1),"",MID(A10,FIND(CHAR(163),A10,1)+1,FIND(" ",A10,1)-1)*1)

and drag down as many rows as needed.

The formula is a bit long, so you might want to simply Copy & Paste from here.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#3
January 21, 2017 at 08:00:43
Hi Mike

Thanks for looking into my issue. The problem I have is all the text is on one cell, not like you have laid out in 14.

It looks like:
A B
1)"Number: CPLUS0000000000
Date: 06/01/2017

Address Amount Tracking code

Customer
Address
Address
£8.76 XXXXXX

exVAT: £7.30
VAT: £1.46
TOTAL: £8.76

===========================================

© 2016 Drop and Collect Ltd. trading as CollectPlus
Registered Number: 06593233 | VAT Number: 946830691 | "
2)"Number: CPLUS0004332304
Date: 06/01/2017

Address Amount Tracking code

Customer Name
Address
Address
Address
£9.76 XXXXXXX

exVAT: £8.14
VAT: £1.62
TOTAL: £9.76

===========================================

© 2016 Drop and Collect Ltd. trading as CollectPlus
Registered Number: 06593233 | VAT Number: 946830691 | "


Report •

Related Solutions

#4
January 21, 2017 at 08:35:42
If all the data is in one cell, then you have a few options.

First is to recopy & paste the data so it appears as my example.
I simply copied the data from you post,
then did a straight paste in cell A1 (Do not use the Formula bar),
or you could try a Paste Special and choose HTML

You can reconfigure the data, using a text editor or Word, so that when it is imported into Excel it will be easier to work with.

It might be possibly a formula could be created to get all the required
data, but it would be very long and very complex and I'm not sure it would work.

You could probably use a Macro to get the data.
Unfortunately, my Macro skills are just above nil, so hopefully
some will jump in with something you could use.

MIKE

http://www.skeptic.com/


Report •

#5
January 21, 2017 at 08:46:19
One other thought, which I haven't tried it yet,
is you might be able to use the Text to Columns function
to separate it into individual columns.

MIKE

http://www.skeptic.com/


Report •

#6
January 21, 2017 at 10:18:21
✔ Best Answer
Here is another idea that seems to work, kinda OK.

Use the Import Text function:
On the Ribbon,
Select Data Tab
Select From Text in the Get External Data section
Select the name of your text file that contains your data.
The Text Import Wizard should pop up
Select Delimited
Click Next
In the Delimiter section select Other
In the input box enter: ALT+156
(Press & hold the ALT key while imputing the numbers 156)
You should see the British Pound symbol appear.
Click Next
Click Finish
A small Import Data box should appear, Click OK

Your data should now appear like:

                         A                                          B
 1) "Number: CPLUS0000000000                               
 2) Date: 01/01/2016                                       
 3)                                                        
 4) Address Amount Tracking code                           
 5)                                                        
 6) Customer Name                                          
 7) Customer Address                                       
 8) Town                                                   
 9) City                                                   
10)                                                            4.62 XX000XX
11)                                                        
12) exVAT:                                                             3.85
13) VAT:                                                               0.77
14) TOTAL:                                                             4.62
15)                                                        
16) ===========================================            
17)                                                        
18) © 2016 Drop and Collect Ltd. trading as CollectPlus    
19) Registered Number: 06593233 | VAT Number: 946830691 | "

Just delete the string "XXOOOXX" in cell B10 and
all you numbers are now separate from the rest.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#7
January 21, 2017 at 10:29:34
Another alternative, is to use the Import Text function and separate each line:

Use the Import Text function:
On the Ribbon,
Select Data Tab
Select From Text in the Get External Data section
Select the name of your text file that contains your data.
The Text Import Wizard should pop up
Select Delimited
Click Next
Do not select any delimiter, leave all the box's empty.
Click Next
Click Finish
A small Import Data box should appear, Click OK

Your data should now be displayed as in my first reply,
with each line of your text separated onto a line in Excel.

Now you can use my original formula to get your numbers.

MIKE

http://www.skeptic.com/


Report •

Ask Question