Forum Discussion
excel cell seperate
How to get text and number and sign seperatly from one excel cell?
for example I have in one cell (-4.5V) and I need to seperate them to three cells (-) & (4.5) &(V)
Ameera , You can use split column option in Power Query under home tab
Or you can
Add a Custom Column to Extract the Sign:
In the Power Query Editor, click on Add Column > Custom Column.
Name the new column (e.g., Sign).
Use the following formula to extract the sign:
if Text.Start([YourColumnName], 1) = "-" then "-" else if Text.Start([YourColumnName], 1) = "+" then "+" else ""
Replace [YourColumnName] with the actual name of your column.
Add a Custom Column to Extract the Number:
Click on Add Column > Custom Column.
Name the new column (e.g., Number).
Use the following formula to extract the number:
Text.Middle([YourColumnName], if Text.Start([YourColumnName], 1) = "-" or Text.Start([YourColumnName], 1) = "+" then 1 else 0, Text.Length([YourColumnName]) - 2)
This formula assumes that the number is between the sign and the unit.
Add a Custom Column to Extract the Unit:
Click on Add Column > Custom Column.
Name the new column (e.g., Unit).
Use the following formula to extract the unit:Text.End([YourColumnName], 1)
1 Reply
Ameera , You can use split column option in Power Query under home tab
Or you can
Add a Custom Column to Extract the Sign:
In the Power Query Editor, click on Add Column > Custom Column.
Name the new column (e.g., Sign).
Use the following formula to extract the sign:
if Text.Start([YourColumnName], 1) = "-" then "-" else if Text.Start([YourColumnName], 1) = "+" then "+" else ""
Replace [YourColumnName] with the actual name of your column.
Add a Custom Column to Extract the Number:
Click on Add Column > Custom Column.
Name the new column (e.g., Number).
Use the following formula to extract the number:
Text.Middle([YourColumnName], if Text.Start([YourColumnName], 1) = "-" or Text.Start([YourColumnName], 1) = "+" then 1 else 0, Text.Length([YourColumnName]) - 2)
This formula assumes that the number is between the sign and the unit.
Add a Custom Column to Extract the Unit:
Click on Add Column > Custom Column.
Name the new column (e.g., Unit).
Use the following formula to extract the unit:Text.End([YourColumnName], 1)