文本到列:在值的第一个数字处分割

问题描述

我有1列，包含大约60个具有值或不同长度的单元格.每个(或至少大多数)值在值中都有一个数字字符.我想将列单元格拆分为更多列，通常使用excel的从tekst到列"功能.

I have 1 column with about 60 cells with values or different length. Each (or at least most) of the values have a numeric characters in the value. I want to split the columns cells into more columns which I normally would do with the 'tekst to columns' function of excel.

但是该函数没有高级选项，可以将第一个数字字符的值分割.可以根据空格，逗号等进行拆分，但这对我没有帮助.

But this function does not have an advanced option of splitting the value at the first numeric character. splitting based on spaces, comma etc. is possible but this does not help me.

有什么方法可以将像元值中第一个数字的像元分为两列?

Is there any way to divide the cells into 2 columns at the first number in the cell value?

我查看了许多其他问题，但没有一个(或其他互联网论坛)帮助我将值拆分为单元格值的第一个数字.

I have looked at numerous other questions but none of them (or other internet fora) have helped me to split the value at the first number of the cell value.

感谢#quantum285的回答.如果字符串包含一个数字，则此例程有效.我将测试字符串更改为firstpart323secondpart.然后part1返回'firstpart32'，而part2返回secondpart.

Thanks #quantum285 for the answer. This routine works if the string contains one number. I changed the teststring to firstpart323secondpart.then part1 returns 'firstpart32' and part2 return secondpart.

我试图了解这段代码中发生了什么，如果我错了，请纠正我:

I tried to understand what happens in this code, please correct me if I'm wrong:

首先，确定字符串的长度.

First, the lenght of the string is determined.

第二，检查此字符串中的每个位置是否为数字.但是这张支票是从右到左丹吗?因此，在 firstpart323secondpart:的情况下，长度为22.
然后isnumeric()检查从1到22的每个位置是否为数字，并在找到数字时停止?

Secondly, for each position in this string is checked if it is numeric or not. But this check is dan from right to left? So in case of firstpart323secondpart: the length is 22.
then isnumeric() checks for every position from 1 to 22 if it is numeric and stops when it finds a number?

如果是，则第1部分是值 i 之前的tekst，其中 i 是找到数字的字符串中从右到左的第一个位置.

If so, part 1 is the the tekst before the value i, where i is the first position from right to left in the string where a number is found.

，则第2部分是同一位置右侧的字符串.

and part 2 is then the string on the right from this same position.

但是，我正在寻找一个例程，该例程可以找到数字从左到右的第一个位置(或从右到左的最后一个位置)...

However, I am looking for a routine which find the first position from left to right (or the last position from right to left) where a number is, ...

所以我现在更改了例程，只需调整for i = 1 to行:

So I changed the routine now, simply adjusting the for i = 1 to line:

Sub test()
For j = 4 To Cells(Rows.Count, 4).End(xlUp).Row
For i = Len(Cells(j, 4)) To 1 Step -1
    If IsNumeric(Mid(Cells(j, 4), i, 1)) Then
        Cells(j, 5) = Left(Cells(j, 4), i - 1)
        Cells(j, 6) = (Right(Cells(j, 4), Len(Cells(j, 4)) - i + 1))
    End If
Next i
Next j

End Sub

这几乎可以完美地工作(除了几个具有多个数字组合的单元格(例如:汤10g 20盒).但是由于这些只是少数几个，我可以手动调整它们.

this almost perfectly works (except for a few cells which have multiple number combinations in the value (like: soup 10g 20boxes). But as these are only a few, I can adjust them by hand.

谢谢！

推荐答案

Sub test()
testString = "firstpart3secondpart"
For i = 1 To Len(testString)
    If IsNumeric(Mid(testString, i, 1)) Then
        part1 = Left(testString, i - 1)
        part2 = (Right(testString, Len(testString) - i))
    End If
Next i
MsgBox (part1)
MsgBox (part2)
End Sub

在循环中使用类似的内容.

Use something like this within your loop.

这篇关于文本到列:在值的第一个数字处分割的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！