Extract text segments from data
I have lot of sheets, and I need to remove text in middle of each cell
My data looks like:
2015-05-06T194803Z_1_LWD000Y24X6QH_RTRWNEV_B_3227-MEXICO-ALIENS-PHOTO
2015-05-06T222036Z_1_LWD0Y24Z6N9_RTRWNEV_B_3318-USA-POLICE-CHICAGO
2015-05-06T224129Z_1_LWD000Y2A56Y1_RTRWNEV_0_4601-IRAN-OIL-MARKET
I want this format:
Column A Column B Column C
5/6/2015 3227 MEXICO-ALIENS-PHOTO
5/6/2015 3318 USA-POLICE-CHICAGO
5/6/2015 4601 IRAN-OIL-MARKET
microsoft-excel
edited Jan 28 at 23:17
fixer1234
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
add a comment |
I have lot of sheets, and I need to remove text in middle of each cell
My data looks like:
2015-05-06T194803Z_1_LWD000Y24X6QH_RTRWNEV_B_3227-MEXICO-ALIENS-PHOTO
2015-05-06T222036Z_1_LWD0Y24Z6N9_RTRWNEV_B_3318-USA-POLICE-CHICAGO
2015-05-06T224129Z_1_LWD000Y2A56Y1_RTRWNEV_0_4601-IRAN-OIL-MARKET
I want this format:
Column A Column B Column C
5/6/2015 3227 MEXICO-ALIENS-PHOTO
5/6/2015 3318 USA-POLICE-CHICAGO
5/6/2015 4601 IRAN-OIL-MARKET
microsoft-excel
edited Jan 28 at 23:17
fixer1234
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24
add a comment |
I have lot of sheets, and I need to remove text in middle of each cell
My data looks like:
2015-05-06T194803Z_1_LWD000Y24X6QH_RTRWNEV_B_3227-MEXICO-ALIENS-PHOTO
2015-05-06T222036Z_1_LWD0Y24Z6N9_RTRWNEV_B_3318-USA-POLICE-CHICAGO
2015-05-06T224129Z_1_LWD000Y2A56Y1_RTRWNEV_0_4601-IRAN-OIL-MARKET
I want this format:
Column A Column B Column C
5/6/2015 3227 MEXICO-ALIENS-PHOTO
5/6/2015 3318 USA-POLICE-CHICAGO
5/6/2015 4601 IRAN-OIL-MARKET
microsoft-excel
edited Jan 28 at 23:17
fixer1234
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
I have lot of sheets, and I need to remove text in middle of each cell
My data looks like:
2015-05-06T194803Z_1_LWD000Y24X6QH_RTRWNEV_B_3227-MEXICO-ALIENS-PHOTO
2015-05-06T222036Z_1_LWD0Y24Z6N9_RTRWNEV_B_3318-USA-POLICE-CHICAGO
2015-05-06T224129Z_1_LWD000Y2A56Y1_RTRWNEV_0_4601-IRAN-OIL-MARKET
I want this format:
Column A Column B Column C
5/6/2015 3227 MEXICO-ALIENS-PHOTO
5/6/2015 3318 USA-POLICE-CHICAGO
5/6/2015 4601 IRAN-OIL-MARKET
microsoft-excel
microsoft-excel
edited Jan 28 at 23:17
fixer1234
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
edited Jan 28 at 23:17
fixer1234
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
edited Jan 28 at 23:17
fixer1234
18.8k144982
edited Jan 28 at 23:17
fixer1234
18.8k144982
edited Jan 28 at 23:17
fixer1234
18.8k144982
18.8k144982
asked Sep 9 '15 at 6:17
NoumanNouman
613
asked Sep 9 '15 at 6:17
NoumanNouman
613
asked Sep 9 '15 at 6:17
NoumanNouman
613
613
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24
add a comment |
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24
add a comment |
1 Answer
1
active
oldest
votes
You can do this by using the following formula in excel if the data you have shown is consistent.
For Column A use=LEFT(A1,10)
Comment: The first 10 digits represent the date
For Column B use=MID(A1,FIND("_B_",A1)+3,4)
Comment: The _B_ if it is consistently available in all the fields you can use that as a marker and extract the 4 digits
OR
You can use the formula=MID(A1,46,4)
Comment: This formula just assumes that your 4 digit data is exactly 46 places from the beginning of the text
For Column C use=MID(A1,FIND(K1,A1)+5,LEN(A1))
Comment: After extracting the previous four digits, the text is offset only by 5 places - provided it is consistent you can extract the text as well
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f970295%2fextract-text-segments-from-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do this by using the following formula in excel if the data you have shown is consistent.
For Column A use=LEFT(A1,10)
Comment: The first 10 digits represent the date
For Column B use=MID(A1,FIND("_B_",A1)+3,4)
Comment: The _B_ if it is consistently available in all the fields you can use that as a marker and extract the 4 digits
OR
You can use the formula=MID(A1,46,4)
Comment: This formula just assumes that your 4 digit data is exactly 46 places from the beginning of the text
For Column C use=MID(A1,FIND(K1,A1)+5,LEN(A1))
Comment: After extracting the previous four digits, the text is offset only by 5 places - provided it is consistent you can extract the text as well
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
add a comment |
You can do this by using the following formula in excel if the data you have shown is consistent.
For Column A use=LEFT(A1,10)
Comment: The first 10 digits represent the date
For Column B use=MID(A1,FIND("_B_",A1)+3,4)
Comment: The _B_ if it is consistently available in all the fields you can use that as a marker and extract the 4 digits
OR
You can use the formula=MID(A1,46,4)
Comment: This formula just assumes that your 4 digit data is exactly 46 places from the beginning of the text
For Column C use=MID(A1,FIND(K1,A1)+5,LEN(A1))
Comment: After extracting the previous four digits, the text is offset only by 5 places - provided it is consistent you can extract the text as well
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
add a comment |
You can do this by using the following formula in excel if the data you have shown is consistent.
For Column A use=LEFT(A1,10)
Comment: The first 10 digits represent the date
For Column B use=MID(A1,FIND("_B_",A1)+3,4)
Comment: The _B_ if it is consistently available in all the fields you can use that as a marker and extract the 4 digits
OR
You can use the formula=MID(A1,46,4)
Comment: This formula just assumes that your 4 digit data is exactly 46 places from the beginning of the text
For Column C use=MID(A1,FIND(K1,A1)+5,LEN(A1))
Comment: After extracting the previous four digits, the text is offset only by 5 places - provided it is consistent you can extract the text as well
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
You can do this by using the following formula in excel if the data you have shown is consistent.
For Column A use=LEFT(A1,10)
Comment: The first 10 digits represent the date
For Column B use=MID(A1,FIND("_B_",A1)+3,4)
Comment: The _B_ if it is consistently available in all the fields you can use that as a marker and extract the 4 digits
OR
You can use the formula=MID(A1,46,4)
Comment: This formula just assumes that your 4 digit data is exactly 46 places from the beginning of the text
For Column C use=MID(A1,FIND(K1,A1)+5,LEN(A1))
Comment: After extracting the previous four digits, the text is offset only by 5 places - provided it is consistent you can extract the text as well
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
answered Sep 9 '15 at 8:22
PrasannaPrasanna
3,12732140
3,12732140
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f970295%2fextract-text-segments-from-data%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
how about a SUBSTITUTE command ?if the text was common replace it with " "
– BlueBerry - Vignesh4303
Sep 9 '15 at 10:24