colume problem

[geezuslvr]

I received an excel work sheet that for some reason the last letter or number in one column showed up as a first letter in the column next to it.

This did not happen with all the rows, just some of them.

i.e.: |angle sto |p 12.15|

how do I correct this?

[FDibbins]

Welcome to the forum "_

Where is this data coming from?

[Richard Buttrey]

Hi,

Difficult to say with certainty since we don't know how the original data was created and entered into Excel. But it looks like it's probably associated with an incorrect data parse.

[AlKey]

Maybe it can be corrected with a simple formula

Please Login or Register  to view this content.

v	A	B	C
1	angle sto	p 12.15	angle stop 12.15

[geezuslvr]

My columns need to be separate.
column A should read "angle stop" as its description.
column B should read "12.15" as the price.

The spreadsheet came from my supplier via email. What I do next is copy the stuff in a program made in Access.

Is there some formula or something else where I could have excel go down the column B and take all the letters and numbers that belong in the last word in column A?

in column B, the space between the letter "p" and its price "12.15" is 8 spaces, so it looks like all i need is a formula to take all the spaces with its information in front of the first numbers in column B and move them to the end of the last entry in column A.

Thanks in advance for all your help

[Richard Buttrey]

Would you upload your workbook so that we can see the problem in context. I'm still not clear whether the string of text you show is actually in one column or two. When you upload the workbook make sure there are several unique examples of the data strings. One example is usually not enough to be certain that a solution is generalised for other strings you may have.

[geezuslvr]

ASCHI 154940 AS2034.014 AS 2034.014.020 WHITE CHAMPION4 1P C 379.47 ea #VALUE!
ASCHI 20055 AS2037.100 AS 2037.100.020 WHITE NEW LEXINGTO N 446.88 ea #VALUE!
ASCHI 62213 AS2073.014 AS 2073.014.020 WHITE FLOWISE4 EL 206.08 ea 222.5664
ASCHI 69059 AS2083.102 AS 2083.102.020 WHITE STANDARD 5' 492.24 ea 531.6192
ASCHI 69224 AS2083.202 AS 2083.202.020 WHITE STANDARD 5' 492.24 ea 531.6192
ASCHI 30255 AS2093.100 AS 2093.100.020 WHITE GLENWALL EL 361.46 ea 390.3768
ASCHI 52061 AS2099.016 AS 2099.016.020 WHITE CADET RF 228.48 ea 246.7584
ASCHI 35590 AS2100.016 AS 2100.016.020 WHITE CADET EL 269.92 ea 291.5136
ASCHI 68915 AS2234.001 AS 2234.001.020 15" HGT UNIV BOWL 85.18 ea 91.9944
ASCHI 59673 AS2254.127 AS 2254.127.020 WHITE AFWALL BOWL 120.40 ea 130.032
ASCHI 59523 AS2256.194 AS 2256.194.020 WHITE AFWALL BOWL 100.80 ea 108.864
ASCHI 407052 AS2257.001 AS 2257.101.020 AFWALL FV BOWL 84.15 ea 90.882
ASCHI 300404 AS2257.528 AS 2257.528.020 WHITE AFWALL - 1.2 8 360.28 ea #VALUE!
ASCHI 58476 AS2282.010 AS 2282.010.020 WHITE BABY DEVORO 122.33 ea 132.1164
ASCHI 11909 AS2294.011 AS 2294.011EC.020 WHITE AFWALL ADA 244.13 ea 263.6604
ASCHI 65570 AS2296.019 AS 2296.019EC.020 WHITE AFWALL ADA 258.56 ea 279.2448
ASCHI 7692 AS2315.016 AS 2315.016.020 WHITE BABY DEVORO 202.65 ea 218.862
ASCHI 59619 AS2366.100 AS 2366.100.020 WHITE CADET TOILET 275.52 ea 297.5616
ASCHI 226758 AS2403.128 AS 2403.128.020 WHITE COMPACT 1PC 276.94 ea 299.0952
ASCHI 278365 AS2403.813 AS 2403.813.020 WHITE CADET3 FW 1P C 338.63 ea #VALUE!
ASCHI 273847 AS2462.016 AS 2462.016.020 WHITE EL CADET PA 239.64 ea 258.8112
ASCHI 272651 AS2462.100 AS 2462.100.020 WHITE EL CADET FW 239.64 ea 258.8112
ASCHI 273778 AS2467.016 A/S 2467.016.020 WHITE EL CADET PA 253.02 ea 273.2616
ASCHI 272667 AS2467.100 A/S 2467.100.020 WHITE EL CADET FW 253.02 ea 273.2616
ASCHI 19438 AS2529.016 AS 2529.016.020 WHITE NEOLO EL 1.6 242.48 ea 261.8784
ASCHI 298651 AS2634.001 AS 2634.001.020 WHITE AFWALL UNIV 109.73 ea 118.5084
ASCHI 316399 AS2753.128 AS 2753.128.020 WHITE EVOLUTION2 126.00 ea 136.08
ASCHI 289422 AS2754.128 AS 2754.128.020 WHITE EVOLUTION2 146.48 ea 158.1984
ASCHI 304623 AS2758.128 AS 2758.128.020 WHITE CADET3 FW AD A 171.15 ea #VALUE!
ASCHI 312552 AS2833.128 AS 2833.128.020 CADET3 FW EL COMBO 196.35 ea 212.058
ASCHI 318395 AS2835.813 AS 2835.813.020 WHITE CADET3 FW RH 185.33 ea 200.1564
ASCHI 294984 AS2849.128 AS 2849.128.020 CADET3 FW RH RF 211.05 ea 227.934
ASCHI 322478 AS2854.128 AS 2854.128.020 WHITE MADERA EC 183.27 ea 197.9316
ASCHI 333990 AS2858.128 AS 2858.128.020 WHITE MADERA EL - 150.94 ea 163.0152
ASCHI 273782 AS2876.016 AS 2876.016.020 WHITE EL YORKVILLE 360.53 ea 389.3724
ASCHI 282330 AS2876.100 AS 2876.100.020 WHITE EL YORKVILLE 360.53 ea 389.3724
ASCHI 273794 AS2878.016 AS 2878.016.020 WHITE RH EL 443.53 ea 479.0124
ASCHI 326382 AS2886.216 AS 2886.216.020 WHT H2OPTION DUAL 240.98 ea 260.2584
ASCHI 290365 AS2887.216 AS 2887.216.020 WHITE H2OPTION DUA L 211.52 ea #VALUE!
ASCHI 296919 AS3005.001 AS 3005.001.020 WHITE CADET3 FW RH 118.65 ea 128.142
ASCHI 103934 AS3016.001 AS 3016.001 WHITE CADET3 ADA BOWL 130.20 ea 140.616
ASCHI 235346 AS3016.001 AS 3016.001.222 LINEN CADET3 FW EL 162.23 ea 175.2084
ASCHI 30868 AS3043.001 AS 3043.001.020 WHITE MADERA 16-1/ 2 100.67 ea #VALUE!
ASCHI 321798 AS3043.528 AS 3043.528.020 WHITE MADERA ADA - 377.34 ea 407.5272
ASCHI 325730 AS3043.660 AS 3043.660.020 WHITE MADERA ADA - 377.34 ea 407.5272
ASCHI 301424 AS3063.001 AS 3063.001.020 WHITE EVOLUTION2 76.65 ea 82.782
ASCHI 297005 AS3068.001 AS 3068.001.020 WHITE EVOLUTION2 F W 97.13 ea #VALUE!
ASCHI 17332 AS3110.016 AS 3110.016.020 WHITE CHAMPION RF 86.63 ea 93.5604
ASCHI 17285 AS3121.016 AS 3121.016.020 WHITE CHAMPION EL 100.80 ea 108.864
ASCHI 302431 AS3312.001 AS 3312.001.020 WHITE HURON HET RH 250.35 ea 270.378
ASCHI 407059 AS3351.001 AS 3351.101.020 WHITE AFWALL EC 91.25 ea 98.55
ASCHI 297378 AS3351.528 AS 3351.528.020 WHITE AFWALL EC - 374.59 ea 404.5572
ASCHI 44349 AS3402.016 AS 3402.016.020 WHITE GLENWALL EL 171.36 ea 185.0688
ASCHI 68471 AS3417.016 AS 3417.016.021 BONE CADET ADA BOW L 197.68 ea #VALUE!
ASCHI 284723 AS3451.001 AS 3451.001.020 WHITE 15" FV BOWL 95.94 ea 103.6152
ASCHI 326178 AS3451.528 AS 3451.528.020 WHITE MADERA EC EL -374.76 ea -404.7408
ASCHI 59560 AS3454.016 AS 3454.016.021 BONE CADET BOWL RF 81.76 ea 88.3008
ASCHI 284722 AS3461.001 AS 3461.001.020 16-1/2 ADA FV BOWL 115.63 ea 124.8804
ASCHI 326664 AS3461.528 AS 3461.528.020 WHITE MADERA EC 392.61 ea 424.0188
ASCHI 329206 AS3461.660 AS 3461.660.020 WHITE MADERA EC 392.61 ea 424.0188
ASCHI 290173 AS3462.001 AS 3462.001.020 WHITE MADERA 126.39 ea 136.5012
ASCHI 302653 AS3463.001 AS 3463.001.020 WHITE MADERA EC 161.84 ea 174.7872
ASCHI 273848 AS3481.001 AS 3481.001.020 WHITE CADET NH PA 57.57 ea 62.1756
ASCHI 273780 AS3483.001 AS 3483.001.020 WHITE CADET PA UNI V 70.95 ea #VALUE!
ASCHI 312275 AS3690.001 AS 3690.001.020 WHITE PRIOLO EL 206.17 0 222.6636
ASCHI 284995 AS3695.001 AS 3695.001.020 WHITE PRIOLO RH EL 274.44 ea 296.3952
ASCHI 273788 AS3701.001 AS 3701.001.020 WHITE YORKVILLE NH 178.46 ea 192.7368
ASCHI 273786 AS3703.001 AS 3703.001.020 WHITE YORKVILLE RH 261.46 ea 282.3768
ASCHI 296642 AS3706.216 AS 3706.216.020 WHITE H2OPTION DUA L 97.73 ea #VALUE!
ASCHI 285824 AS4006.016 AS 4006.016.020 WHITE COLONY 73.50 ea 79.38
ASCHI 60263 AS4098.100 AS 4098.100.020 WHITE CADET TANK 190.10 ea 205.308
ASCHI 60555 AS4098.800 AS 4098.800.020 WHITE CADET PA TAN K 191.44 ea #VALUE!
ASCHI 70941 AS4110.016 AS 4110.016.021 BONE CADET TANK 1. 6 112.00 ea #VALUE!
ASCHI 943 AS4110.800 AS 4110.800.020 WHITE CADET TANK 89.60 ea 96.768
ASCHI 44251 AS4114.016 AS 4114.016.020 WHITE CADET TANK 100.24 ea 108.2592
ASCHI 14962 AS4114.800 AS 4114.800.020 WHITE CADET TANK 100.24 ea 108.2592
ASCHI 299205 AS4142.100 "A/S 4142.100.020 WHITE PA TANK 1 .1" 182. 07 e #VALUE!
ASCHI 321103 AS4142.601 "AS 4142.601.020 WHITE \PA TANK 1 .1" 272. 67 e #VALUE!
ASCHI 304008 AS4142.801 "A/S 4142.801.020 WHITE PA TANK 1 .1" 186. 09 e #VALUE!
ASCHI 281800 AS4281.014 AS 4281.014.020 WHITE 141.96 ea 153.3168
ASCHI 296643 AS4339.216 AS 4339.216.020 WHITE H2OPTION 113.79 ea 122.8932

The columns with the #VALUE! are the messed up columns