| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP! How to put an IF formula with "starts with" instead of bigger than or equal to
Hi,
I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X ..MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! How to put an IF formula with "starts with" instead of biggerthan or equal to
I'm not sure I understand...
with the data in A1 =if(left(a1,1)=".",mid(a1,2,6),a1) or =if(left(a1,1)=".",mid(a1,2,6),"") debbedeb wrote: Hi, I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X .MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! How to put an IF formula with "starts with" instead of bigger than or equal to
What I want is that if you have all this data, that only in the cells
next to the data with a starting dot there will be the formula used to. so if there is not a dot at the beginning of the cell there then the cell is empty and if it does start with a dot; that the mid formula only gives me the 2nd to the 6th digit in this cell. Do you understand what I mean?? Dave Peterson wrote: I'm not sure I understand... with the data in A1 =if(left(a1,1)=".",mid(a1,2,6),a1) or =if(left(a1,1)=".",mid(a1,2,6),"") debbedeb wrote: Hi, I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X .MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! How to put an IF formula with "starts with" instead of bigger than or equal to
i want to basically copy the formula in the entire column without
having to delete it everytime for the ones that dont have the information in the beginning that i want debbedeb wrote: What I want is that if you have all this data, that only in the cells next to the data with a starting dot there will be the formula used to. so if there is not a dot at the beginning of the cell there then the cell is empty and if it does start with a dot; that the mid formula only gives me the 2nd to the 6th digit in this cell. Do you understand what I mean?? Dave Peterson wrote: I'm not sure I understand... with the data in A1 =if(left(a1,1)=".",mid(a1,2,6),a1) or =if(left(a1,1)=".",mid(a1,2,6),"") debbedeb wrote: Hi, I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X .MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! How to put an IF formula with "starts with" instead of biggerthan or equal to
Did you try any of the suggestions?
Did any of them do what you wanted? debbedeb wrote: What I want is that if you have all this data, that only in the cells next to the data with a starting dot there will be the formula used to. so if there is not a dot at the beginning of the cell there then the cell is empty and if it does start with a dot; that the mid formula only gives me the 2nd to the 6th digit in this cell. Do you understand what I mean?? Dave Peterson wrote: I'm not sure I understand... with the data in A1 =if(left(a1,1)=".",mid(a1,2,6),a1) or =if(left(a1,1)=".",mid(a1,2,6),"") debbedeb wrote: Hi, I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X .MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
HELP! How to put an IF formula with "starts with" instead of bigge
Is there an instance where the line starts with multiple dots or commas
?(e.g. ...,,,MADZBIBCGDCACDAC) and u want to get only the 2nd upto 7th Letter = 6 Letters (e.g. ADZBIB) OR your data is strictly either of the two instances? (e.g.) 1) .MADZBIBCGDCACDAC (formula result is ADZBIB) 2) MADZBIBCGDCACDAC (formula result is Blank cell "") please clarify a little. "debbedeb" wrote: Hi, I have the following data (x200 with all a different information that I want to have an IF formula in the colomn next to the specific data when if there is for instance for the line that says .MADZBIB that I want only Excel to take the MID(cell;2ndletter till the 7nd letter) but ONLY if the line starts with a dot (.) and for the ones that do not start with a dot I want to Excel to do nothing. After that I want Excel to place the first 7 digits after the dot in the cell. The input: Message is displayed from SITA archiv: 6353636 08.11.2006 01:05:23 QD HAMBI7X ..MADZBIB 072349 NOV06/AGNT9999/PID43007 #CAM S1BOOKED/IBM/D4296/USD/1/ 441/48011057301/D5331034701200/1/EA/2790.00/08116/MFR FAPE3/PRI AOG/ ACN 1027/PKG 0/VRN ESA28017648 =(IF(".";"MID(cell;2;7))")) does not work obviousbly because the line only starts with a dot. How do I put this in the IF formula? That instead of bigger than or smaller than or =, one for it starts with........ Who knows???? I can not seem to figure it out and it has been taking up a lot of my time to get it right... I really hope someone can help me! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| If the result of a formula is negative make equal to zero | New Users to Excel | |||
| formula to workout equal to or greater than | Excel Worksheet Functions | |||
| Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Linear Mode
