| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
help with spaces in functions (Biff)
The formula you gave me =substitute(mid(f1,find("~",substitute(f1,"
","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#2
|
|||
|
|||
|
Hi!
Let me see if I can find the original post. I'll get back to you. Biff "bill gras" wrote in message ... The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#3
|
|||
|
|||
|
The original post:
************************************ I have in cell F1 open 4+ 60k in F2 c6 50k in F3 c4 522.5k I need the last part of each cell without the "K' eg: F1 open 4+ 60k = open(space)4+(space space)60k result 60 F2 c6 50k = c6(space space)50k result 50 F3 c4 522.5k = c4(space)522.5k result 522.5 The list goes down to 300 rows I have tried some find functions but can not get the correct result for each row hope some one can help ************************************ Ok, can you post some more examples of the data from the cells where you're getting the error? Biff "Biff" wrote in message ... Hi! Let me see if I can find the original post. I'll get back to you. Biff "bill gras" wrote in message ... The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#4
|
|||
|
|||
|
Hi Biff
I import a file to excel which contains data like this: F1 OPEN 4+ 60K F2 "rubish text" (crap I dont need) F3 OPEN 4+ 50k F4 "rubish text" F5 "rubish text" F6 C6 50K F7 "rubish text" F8 C4 4+ 22.7K F9 "rubish text" F10 C4 22.5K F11 "rubish text" F12 "rubish text" F13 "rubish text" F14 LY1 25K F15 "rubish text" Down to 300 rows all entries are in rows the rows are at random and all entries are all in capital letters Your formula gives me F1 60 F2 #VALUE! F3 #VALUE! F4 #VALUE! F5 #VALUE! F6 50 F7 #VALUE! F8 #VALUE! F9 #VALUE! F10 22.5 F11 #VALUE! F12 #VALUE! F13 #VALUE! F14 #VALUE! F15 #VALUE! I also need to multiply the result by other numbers I just realised that I did not give you the rows with rubish text Sorry Biff my stupid mistake Hope you can still help me regards Bill Gras -- bill gras "bill gras" wrote: The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#5
|
|||
|
|||
|
Hi Bill!
Take a look at this screencap: http://img210.imageshack.us/img210/8...numbers1le.jpg As you can see, every entry that ends with numberK is extracted. The cells where you see #VALUE! correspond to your entry of "rubbish text". Since those cells don't contain a number to extract the formula will return an error. In the next column over is the same formula but I just hide the #VALUE! errors. So, the formula does work. I'm wondering if the problem your having might be caused by the importing. There may be unseen characters in the string that look like standard spaces but might be some other type of blank character. The formula is explicitly looking for CHAR(32) standard spaces. Can you send me a copy of this file? I don't need the whole thing. Maybe just part of the sheet that contains this particular data. If you want to do that let me know how I can contact you. My email address is bogus so don't try using it. You'll just get bounced. Biff "bill gras" wrote in message ... Hi Biff I import a file to excel which contains data like this: F1 OPEN 4+ 60K F2 "rubish text" (crap I dont need) F3 OPEN 4+ 50k F4 "rubish text" F5 "rubish text" F6 C6 50K F7 "rubish text" F8 C4 4+ 22.7K F9 "rubish text" F10 C4 22.5K F11 "rubish text" F12 "rubish text" F13 "rubish text" F14 LY1 25K F15 "rubish text" Down to 300 rows all entries are in rows the rows are at random and all entries are all in capital letters Your formula gives me F1 60 F2 #VALUE! F3 #VALUE! F4 #VALUE! F5 #VALUE! F6 50 F7 #VALUE! F8 #VALUE! F9 #VALUE! F10 22.5 F11 #VALUE! F12 #VALUE! F13 #VALUE! F14 #VALUE! F15 #VALUE! I also need to multiply the result by other numbers I just realised that I did not give you the rows with rubish text Sorry Biff my stupid mistake Hope you can still help me regards Bill Gras -- bill gras "bill gras" wrote: The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#7
|
|||
|
|||
|
-- bill gras "bill gras" wrote: Hi Biff You can contact me by email thank you Bill Gras p s Must be something with the import because it does not work properly. When I typed in the same as you did without the import and it works fine. -- bill gras "Biff" wrote: Hi Bill! Take a look at this screencap: http://img210.imageshack.us/img210/8...numbers1le.jpg As you can see, every entry that ends with numberK is extracted. The cells where you see #VALUE! correspond to your entry of "rubbish text". Since those cells don't contain a number to extract the formula will return an error. In the next column over is the same formula but I just hide the #VALUE! errors. So, the formula does work. I'm wondering if the problem your having might be caused by the importing. There may be unseen characters in the string that look like standard spaces but might be some other type of blank character. The formula is explicitly looking for CHAR(32) standard spaces. Can you send me a copy of this file? I don't need the whole thing. Maybe just part of the sheet that contains this particular data. If you want to do that let me know how I can contact you. My email address is bogus so don't try using it. You'll just get bounced. Biff "bill gras" wrote in message ... Hi Biff I import a file to excel which contains data like this: F1 OPEN 4+ 60K F2 "rubish text" (crap I dont need) F3 OPEN 4+ 50k F4 "rubish text" F5 "rubish text" F6 C6 50K F7 "rubish text" F8 C4 4+ 22.7K F9 "rubish text" F10 C4 22.5K F11 "rubish text" F12 "rubish text" F13 "rubish text" F14 LY1 25K F15 "rubish text" Down to 300 rows all entries are in rows the rows are at random and all entries are all in capital letters Your formula gives me F1 60 F2 #VALUE! F3 #VALUE! F4 #VALUE! F5 #VALUE! F6 50 F7 #VALUE! F8 #VALUE! F9 #VALUE! F10 22.5 F11 #VALUE! F12 #VALUE! F13 #VALUE! F14 #VALUE! F15 #VALUE! I also need to multiply the result by other numbers I just realised that I did not give you the rows with rubish text Sorry Biff my stupid mistake Hope you can still help me regards Bill Gras -- bill gras "bill gras" wrote: The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#8
|
|||
|
|||
|
OK, sent you an email.
Biff "bill gras" wrote in message ... -- bill gras "bill gras" wrote: Hi Biff You can contact me by email thank you Bill Gras p s Must be something with the import because it does not work properly. When I typed in the same as you did without the import and it works fine. -- bill gras "Biff" wrote: Hi Bill! Take a look at this screencap: http://img210.imageshack.us/img210/8...numbers1le.jpg As you can see, every entry that ends with numberK is extracted. The cells where you see #VALUE! correspond to your entry of "rubbish text". Since those cells don't contain a number to extract the formula will return an error. In the next column over is the same formula but I just hide the #VALUE! errors. So, the formula does work. I'm wondering if the problem your having might be caused by the importing. There may be unseen characters in the string that look like standard spaces but might be some other type of blank character. The formula is explicitly looking for CHAR(32) standard spaces. Can you send me a copy of this file? I don't need the whole thing. Maybe just part of the sheet that contains this particular data. If you want to do that let me know how I can contact you. My email address is bogus so don't try using it. You'll just get bounced. Biff "bill gras" wrote in message ... Hi Biff I import a file to excel which contains data like this: F1 OPEN 4+ 60K F2 "rubish text" (crap I dont need) F3 OPEN 4+ 50k F4 "rubish text" F5 "rubish text" F6 C6 50K F7 "rubish text" F8 C4 4+ 22.7K F9 "rubish text" F10 C4 22.5K F11 "rubish text" F12 "rubish text" F13 "rubish text" F14 LY1 25K F15 "rubish text" Down to 300 rows all entries are in rows the rows are at random and all entries are all in capital letters Your formula gives me F1 60 F2 #VALUE! F3 #VALUE! F4 #VALUE! F5 #VALUE! F6 50 F7 #VALUE! F8 #VALUE! F9 #VALUE! F10 22.5 F11 #VALUE! F12 #VALUE! F13 #VALUE! F14 #VALUE! F15 #VALUE! I also need to multiply the result by other numbers I just realised that I did not give you the rows with rubish text Sorry Biff my stupid mistake Hope you can still help me regards Bill Gras -- bill gras "bill gras" wrote: The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
|
#9
|
|||
|
|||
|
If anyone is following this thread Bill was a victim of the dreaded
CHAR(160)! Suggested he use the Find/Replace commands and also refered him to David McRitchie's TRIMALL macro. Biff "Biff" wrote in message ... OK, sent you an email. Biff "bill gras" wrote in message ... -- bill gras "bill gras" wrote: Hi Biff You can contact me by email thank you Bill Gras p s Must be something with the import because it does not work properly. When I typed in the same as you did without the import and it works fine. -- bill gras "Biff" wrote: Hi Bill! Take a look at this screencap: http://img210.imageshack.us/img210/8...numbers1le.jpg As you can see, every entry that ends with numberK is extracted. The cells where you see #VALUE! correspond to your entry of "rubbish text". Since those cells don't contain a number to extract the formula will return an error. In the next column over is the same formula but I just hide the #VALUE! errors. So, the formula does work. I'm wondering if the problem your having might be caused by the importing. There may be unseen characters in the string that look like standard spaces but might be some other type of blank character. The formula is explicitly looking for CHAR(32) standard spaces. Can you send me a copy of this file? I don't need the whole thing. Maybe just part of the sheet that contains this particular data. If you want to do that let me know how I can contact you. My email address is bogus so don't try using it. You'll just get bounced. Biff "bill gras" wrote in message ... Hi Biff I import a file to excel which contains data like this: F1 OPEN 4+ 60K F2 "rubish text" (crap I dont need) F3 OPEN 4+ 50k F4 "rubish text" F5 "rubish text" F6 C6 50K F7 "rubish text" F8 C4 4+ 22.7K F9 "rubish text" F10 C4 22.5K F11 "rubish text" F12 "rubish text" F13 "rubish text" F14 LY1 25K F15 "rubish text" Down to 300 rows all entries are in rows the rows are at random and all entries are all in capital letters Your formula gives me F1 60 F2 #VALUE! F3 #VALUE! F4 #VALUE! F5 #VALUE! F6 50 F7 #VALUE! F8 #VALUE! F9 #VALUE! F10 22.5 F11 #VALUE! F12 #VALUE! F13 #VALUE! F14 #VALUE! F15 #VALUE! I also need to multiply the result by other numbers I just realised that I did not give you the rows with rubish text Sorry Biff my stupid mistake Hope you can still help me regards Bill Gras -- bill gras "bill gras" wrote: The formula you gave me =substitute(mid(f1,find("~",substitute(f1," ","~",len(f1)-len(substitute(f1," ",""))))+1,255),right(f1,1),"")*1 works partley,it will only give me the first 3 rows with a result and the other 297 rows have a #value! result could you please help me I have tried to change parts of the formula but no luck thank you -- bill gras |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
| how do I remove empty spaces trailing a text string? | Excel Worksheet Functions | |||
| Default User Defined Functions - How? | Excel Discussion (Misc queries) | |||
| Stripping out imbedded spaces in a cell/row | Excel Worksheet Functions | |||
| 3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
Linear Mode
